SQL Server 2008 Unique Column that is Case Sensitive

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.


  • 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:


    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.



    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:


    and no error about the unique index being violated.