Tuesday, May 3, 2011

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

From stackoverflow
  • 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)

    Eric : A note about collation: I've had to write way too many COLLATE statements as a result of a source table being Latin1_General_CS_AS and one being Latin1_General_CI_AI. This really only applies if you use SSIS to move data around, or at least, that's the only spot I've ever hit an issue in.
  • 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.


Post a Comment