Removing Duplicates from a Table in SQL Server

Checking for Duplicates

On any version of SQL Server, you can identify duplicates using a simple query, with GROUP BY and HAVING, as follows:

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

SELECT  data
      , COUNT(data) nr
FROM    @table
GROUP BY data
HAVING  COUNT(data) > 1

Removing Duplicate Rows in SQL Server

 

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

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