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
+6
A:
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)
Anton Gogolev
2009-04-30 06:48:13
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.
Eric
2009-04-30 06:53:51
A:
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.
paxdiablo
2009-04-30 06:50:18