Database Administration Blog

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

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish