Checking For Duplicate Records in Sql Server Table

Reading Time: 2 minutes

I often find myself needing to check if a table is unique with respect to a particular column. For instance, if I have a table of customer data I want to ensure that a customer only exists once. There are several ways that you can do this.  If I want to write a quick select if usually looks like this.

A while back I got tired of writing the above query over and over, so I created a stored procedure (SP). The SP below take two arguments, a table name and a column name and will determine if your column is unique. Currently it is written using a   count(*)  , which is not meant for large tables, i.e. > 2,000,000,000 rows .

There is one more method that I commonly use to determine if my table is unique and that is an index.

So, now you have three different ways to check your table to make sure that your column is unique. Keep in mind that adding an index will take up additional space, where methods 1 and 2 will not.

Leave a Comment

Filed under Sql Server

Leave a Reply