SQL Server 2008 Unique Column that is Case Sensitive

Question

Is there a way to make a column both UNIQUE and Case Sensitive?

I want to be able to put

abcde and ABCDE

in a unique column.

Answer 1

The uniqueness can be enforced with a unique constraint.

Whether or not the unique index is case-sensitive is defined by the server's (or the table's) collation.

You can get the current collation of your database with this query:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation; 

and you should get something like:

SQLCollation ———————————— SQL_Latin1_General_CP1_CI_AS 

Here, the "CI_AS" at the end of the collation means: CI = Case Insensitive, AS = Accent sensitive.

This can be changed to whatever you need it to be. If your database and/or table does have a case-sensitive collation, I would expect that the uniqueness of your index will be case-sensitive as well, e.g. your abcdef and ABCDEF should be both acceptable as unique strings.

Marc

UPDATE:

I just tried this (SQL Server 2008 Developer Edition x64) - works for me (my database is generally using the "Latin1_General_CI_AS collation, but I can define a different one per table / per VARCHAR column even):

CREATE TABLE TestUnique     (string VARCHAR(50) COLLATE SQL_Latin1_General_Cp1_CS_AS)  CREATE UNIQUE INDEX UIX_Test ON dbo.TestUnique(string)  INSERT INTO dbo.TestUnique(string) VALUES ('abc') INSERT INTO dbo.TestUnique(string) VALUES ('ABC')  SELECT * FROM dbo.TestUnique 

and I get back:

string ABC abc 

and no error about the unique index being violated.