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.
-- Method 1 Select count(*),Count(Distinct ColumnofInterest) From Table
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 .
-- Method 2 USE [YourDataBase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create Procedure [dbo].[DupeChecker] @Table nvarchar(128), @Column nvarchar(128) as -- Construct SQL Statement Using Name Provided Declare @MyQuery as nvarchar(max)=' Select @DupeFlag =case when count(*) = count(Distinct ' + @Column + ') then 0 else 1 end From ' + @Table + ' with(nolock) ' Declare @MyNumber smallint Set @MyNumber = 0 exec sp_executesql @MyQuery, N'@DupeFlag smallint out', @MyNumber out -- Raise Error if Table is not uqniue Declare @ErrorText Varchar(100) Set @ErrorText = 'Your column ' + @Column + ' on table ' + @Table + ' is not unique.' If (@MyNumber =1) Begin RAISERROR(@ErrorText,16,1) RETURN End -- If unique print a message to confirm Declare @ErrorText2 Varchar(100) Set @ErrorText2 = 'Your column ' + @Column + ' on table ' + @Table + ' is unique.' If (@MyNumber =0) Begin Print (@ErrorText2) End GO
There is one more method that I commonly use to determine if my table is unique and that is an index.
-- Method 3 Create Unique NonClustered Index [NCI_Dupe_Checker] on TableName ( MyColumn )
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.