[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.
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
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..."
More Related questions