case insensitive collation

Question

New to SQL Server; experienced with Oracle.

Looking for a more Oracle-like behavior.  Production application when running on MSS requires configuring case-insensitive collation, but nothing special on Oracle.

Issues come about when data is mixed case.  We want primary keys to accept data that is case sensitive, but the SQL parsing other than string literals to ignore case.  I understand the issue with SQL parsing is like this:

select column from table where <conditions>

select COLUMN from TABLE where <conditions>

With case sensitive collation on MSS, one of these works; the other doesn't.  With Oracle, both work.

For primary keys, we can't have data like this (with case-insensitive collation to resolve the SQL parsing issue above):

MG

mg

Isn't there some way to configure the system to get both items to work properly.  Not interested in something that is code intensive as there are 500+ tables in the application and it would be impractical to hand-code things.  Application developers wouldn't consider a solution that requires full path code execution for every query that gets generated or is configured on particular web pages.  (the reason why they elected to use case-insensitive collation to deal with the SQL parsing issue).

Thanks

Answer 1

Collation is the attribute of a column. So, you can setup your tables to use case-sensitive collation for columns where you need case-sensitive and case-insensitive for other columns.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

So lets say I have a column called unitsid and I set it up to be case-sensitive, would this apply to the data or the column name also?  That is, I want both of these statements to work:

select unitsid from units where <conditions>

select UNITSID from units where <conditions>

and I want to establish a primary key on this table/column so I can store MG and mg without getting a constraint violation.

Answer 3

It will apply to the data in the column. I hope your SQL Server default collation is case insensitive because otherwise you need to always provide correct casing for all SQL Server objects (table names, functions, procedures, views, etc.) and it will be a major PITA.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Yes, the default collation is setup to be case insensitive for the exact reason you mentioned.