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.