How to Find and Remove Duplicate Column Records in a Table Row (SQL Server)

Here's a couple quick SQL database tips for finding and deleting duplicate values in a SQL Server table.

To find duplicates in a Column use the following SQL:

SELECT ColName1, COUNT(*) TotalCount
FROM TableName
GROUP BY ColName1 HAVING (COUNT(ColName1) > 1)

Note: Using COUNT(*) to find duplicate rows allows the query to find duplicates if ColName1 excepts NULL values.

To find duplicates in a Column and return all columns from the Table use the follow SQL:

SELECT        t1.*
FROM            TableName AS t1
WHERE        (ColumnName1 IN
            (SELECT        ColumnName1
            FROM            TableName AS t2
            GROUP BY ColumnName1
            HAVING         (COUNT(ColumnName1) > 1)))

--------------------------------------------------------------

To get Total Duplicates Use the following SQL:

SELECT COUNT(*) as TotalDuplicates FROM
(
SELECT ColName1, COUNT(*) TotalCount
FROM TableName
GROUP BY ColName1
HAVING COUNT(*) > 1
) as t

--------------------------------------------------------------

To Delete Duplicate Records or Rows use the followng SQL:

DELETE
 FROM TableName
 WHERE ID NOT IN
 (
 SELECT MAX(ID)
 FROM TableName
 GROUP BY DuplicateColumn1, DuplicateColumn2
)

Note: To use the SQL code above the table must have an identity column. The Identity Column in the example above is named "ID" and is used to identify the duplicate records.