Removing Duplicate Rows in SQL Server for Tables with no primary key

When you have duplicates in a table that has no primary key defined, and you are using an older version of SQL Server, such as SQL Server 2000, you do not have an easy way to identify a single row. Therefore, you cannot simply delete this row by specifying a WHERE clause in a DELETE statement.

You can, however, use the SET ROWCOUNT 1 command, which will restrict the subsequent DELETE statement to removing only one row. For example:

DECLARE @table TABLE (dataVARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')INSERT INTO @table VALUES ('duplicate row')INSERT INTO @table VALUES ('duplicate row')

SET ROWCOUNT 1DELETE FROM @table WHERE data = 'duplicate row'SET ROWCOUNT 0

In the above example, only one row is deleted. Consequently, there will be one remaining row with the content “duplicate row”. If you have more than one duplicate of a particular row, you would simply adjust the ROWCOUNT accordingly. Note that after the delete, you should reset the ROWCOUNT to 0 so that subsequent queries are not affected.