How to make a column case sensitive in sql 2005 or 2008

Question

Is it possible to change the default collation based on a column? i want to make 1 column case sensitive but all the others not

Answer 1

ALTER TABLE ALTER COLUMN allows to change collation for a single column:

alter table Foo alter column Bar ntext collate Latin1_General_CS_AS 

(collation might be incorrect)

Answer 2

I don't specifically know SQL Server, but the generally accepted DBMS practice (for compatibility) would be to either:

• put insert and update triggers on the table so that they're stored in the case you want.
• use generated columns to store another copy of the column in the case you want.

There may be a faster way to do it in SQL Server but you should be careful of solutions that push workload into the SELECT statements - they never scale well. It's almost always better doing this as part of inserts and updates since that's the only time data changes - doing it that way minimizes the extra workload.