SQL Server 2008 Unique Column That Is Case Sensitive

SQL Server 2008 Unique Column that is Case Sensitive

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.

T-SQL: How do I create a unique key that is case sensitive?

This will change the column to be case sensitive. I don't think there's any change to your constraint...

ALTER TABLE mytable 
ALTER COLUMN mycolumn VARCHAR(10) 
COLLATE SQL_Latin1_General_CP1_CS_AS

Any selects or joins on this column will become case sensitive as a result of this operation.

The database is case sensitive in unique constraints but not in select

I would take a look at this forum post and validate that the fields in your unique constraint are truly case-sensitive: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/6de23802-eb3b-4759-9d4d-a796c69e6271

You can query for the collation of the field (in case the table was created case-insensitive):

select      name, collation_name
from        sys.columns
where       object_id in (select object_id from sys.objects where name = '<table name>') and name = '<field name>'

Post back if this doesn't resolve the issue and I can add some tests.

How to make Password column in Sql Server 2008 case sensitive?

This is where a collation could come into play.

As others pointed out, note that storing passwords

in plain text is far from being a good idea.

SELECT [Password]
FROM [Users]
WHERE [Password] COLLATE Latin1_General_CS_AS = 'PaSSWord'

See also:

http://msdn.microsoft.com/en-us/library/ms144250.aspx

http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

http://www.sqlservercentral.com/blogs/rocks/2012/01/09/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as/

How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

Can be done via changing the Collation. By default it is case insensitive.

Excerpt from the link:

SELECT 1
FROM dbo.Customers
WHERE   CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
    AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS

Or, change the columns to be case sensitive.

How do you impose a case insensitive unique constraint on a Firebird field value?

You can add a unique index on lower of email column on the table like so:

create unique index email_unq_idx on agent computed by (lower(email));

---

Related Topics