[ACCEPTED]-SQL Server 2008 Unique Column that is Case Sensitive-sql-server-2008

The uniqueness can be enforced with a unique 20 constraint.

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

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

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

and 16 you should get something like:

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

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

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

Marc

UPDATE:

I 7 just tried this (SQL Server 2008 Developer 6 Edition x64) - works for me (my database 5 is generally using the "Latin1_General_CI_AS 4 collation, but I can define a different 3 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 2 I get back:

string
ABC
abc

and no error about the unique 1 index being violated.

In case some one needs to do it on an existing 2 table which already has a unique key/index defined on a 1 varchar/nvarchar column, here is the script.

ALTER TABLE [YourTable] DROP CONSTRAINT [UIX_YourUniqueIndex]
GO

ALTER TABLE [YourTable] ALTER COLUMN [YourColumn] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL;
GO

ALTER TABLE [YourTable] ADD  CONSTRAINT [UIX_YourUniqueIndex] UNIQUE NONCLUSTERED 
(
    [YourColumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I needed to import data from a case sensitive 9 database. When I tried to put the primary 8 key on the column that is the primary key 7 on the source I couldn't do it because of 6 duplicate keys. I changed the collation 5 for the column (varchar) to case sensitive 4 (Right click on the table, choose Design, highlight 3 the column you want to change and click 2 on the elipsis in Collation) and now it 1 works fine. (SQL Server 2008 R2 64 bit).

Thanks @Devraj Gadhavi for the step by step 13 as this is exactly what I needed to do as 12 well. I was about to make those scripts 11 but then (using SSMS 2008R2), I achieved 10 the same in a more lazy :-) way. In the 9 tree view I located my table and column 8 and then right clicked on the column I wanted 7 to change the collation on and chose 'Modify'. In 6 the displayed window, I changed the collation 5 in the properties to the case sensitive 4 one, then anywhere in the open space at 3 the top section of the window (where the 2 columns are listed in table form) I right 1 clicked and chose "Generate Change Script..."