sqlsql-servert-sqlsql-server-2008

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.


Solution

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