SQL Server – How to find duplicate values in a table?

With the SQL statement below you can find duplicate values in any table, just change the tablefield into the column you want to search and change the table into the name of the [gs table] you need to search. In your recordset you will see the tablefield and how many times it is found as a duplicate.

  
SELECT tablefield, COUNT(tablefield) AS dup_count

  
FROM table

  
GROUP BY tablefield

  
HAVING (COUNT(tablefield) > 1)

Some further tempering with the statement gets the complete records that are double. (yeah yeah.. no * should be used in the SELECT) It's just for demonstrating folks!!

  
SELECT *

  
FROM table

  
WHERE tablefield IN (

  
 SELECT tablefield

  
 FROM table

  
 GROUP BY tablefield 
 HAVING (COUNT(tablefield ) > 1)

  
)

To go even further in the process and DELETE every double [gs record] we could do something like make a temporary table, insert the double records, delete it from the original [gs table] and insert the saved single records from the temporary table.