SQL: Change the Collation of a Table Column to Case Insensitive or Case Sensitive

7/15/2015 8:39:17 PM

SQL Server by default will set the collation to Latin Case Insensitive (at least for a US install). This is usually ok but recently, after a move to Salesforce, we had to store the Salesforce Account Ids. These Ids are case sensitive. As your account numbers grow, you will inevitably get case insensitive "duplicates." To deal with this, we changed our SalesforceAccountId column to a case sensitive column. The following 2 scripts will change a column back and for between case sensitive (Latin1_General_CS_AS) and case insensitive (Latin1_General_CI_AS).

--change column to case sensitive "CS"
ALTER TABLE dbo.Users ALTER COLUMN SalesforceAccountId
nvarchar(50) COLLATE Latin1_General_CS_AS NULL;


--change column to case insensitive "CI"
ALTER TABLE dbo.Users ALTER COLUMN SalesforceAccountId
nvarchar(50) COLLATE Latin1_General_CI_AS NULL;