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….