SQL Server Collation

When you’re installing SQL Server, and you get to the screen where you have to choose the Collation Settings, you’re probably just accepting the default of “Dictionary order, case-insensitive, for use with 1252 Character set,” right?

Guess what? They’re serious about that being case-insensitive, i.e., any SQL statements you may run against that server and its databases aren’t case-sensitive … so any UPPER or LOWER functions used in your WHERE-clause comparisons are just being ignored.

That’s fine, except for when you want to be doing case-sensitive string comparisons.

So if you’ve set up a server instance/database like that, and you need to run a case-sensitive query (e.g., in the WHERE clause of a user-defined function or a stored procedure), how do you do that? With the T-SQL COLLATE function and a case-sensitive (CS) collation name, like so:

SELECT Account FROM sysdba.Account
WHERE Account LIKE 'a%'
COLLATE SQL_Latin1_General_Cp1_CS_AS

I haven’t tested whether the SLX Provider can handle the COLLATE function—and would be quite pleasantly surprised if it can….

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: