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