CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
MichaelD
MichaelD

Reputation: 8787

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

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

Upvotes: 17

Views: 30180

Answers (3)

Bhavik Gada
Bhavik Gada

Reputation: 1

The answer to your question is yes, already stated above by Anton Gogolev.

Additional Info:

Here is a how you can find list of Collation supported by your SQL Server based on its version.

select name, 
       COLLATIONPROPERTY(name, 'CodePage') as Code_Page, 
       description
from   sys.fn_HelpCollations()

what is the meaning of Kanatype Sensitive KS and width sensitive

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 882406

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.

Upvotes: 3

Anton Gogolev
Anton Gogolev

Reputation: 115857

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)

Upvotes: 22

Related Questions