How Do I Find Duplicate Rows?

Q: I’m trying to add a primary key to a table that I imported into SQL Server, but I can’t because SQL Server Management Studio (SSMS) is telling me that there are duplicate key values. I don’t want to have duplicate keys. How can I find out which rows in the table contain duplicate values?

A: The easiest way to find the duplicate values is by running a T-SQL query that groups like values together, aggregates their count, and then selects only those values with a count that’s greater than one. The following T-SQL statement shows how to use this method to find duplicate values in the table named Articles where the potential key columns are columns named ArticleID and Status:

 
 SELECT ArticleID, Status, count(*)
 FROM Articles
 GROUP BY ArticleID, Status
 HAVING count(*) > 1

Comments

Plain text