Sometimes we create table without any primary key and duplicate records are entered in the table. When we have two records in the database with no difference what so ever it’s problematic to distinguish between rows. In these case the other row should be deleted. In SQL Server 2000 we have to use the Rowcount to limit the number of rows affected by the command.

[Note: Remember that the default value is 0, which means that there is no limit of the number of rows returned]

But we can change the value before using the command. We should also remember to change the value back after we are done with the command. To delete 2 rows with same record we need to set the value of Rowcount to 1 and issue a delete command for the record.

In SQL Server 2005 there is an enhancement for this purpose. We can use the top keyword with the delete command. Here is an example

DELETE TOP(1) FROM dbo.duplicateRecord WHERE ID = 1000

So in SQL Server2005 we should avoid the use of the Rowcount command and use the top keyword to delete duplicate records. Also Microsoft has said that the Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Hence we should avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in any new development work, and plan to modify applications that currently use it.

Hope this helps
Thanks
Vikram