MS SQL 2000 is Case Sensative?!?!?! 1

[hwkranger]

Salutations!

I've been using SQL for a little while and I've been in a couple of shops, but I recently changed jobs and now at the new place everything on their SQL servers is case sensative.

I can't figure out why (or how) they would do this. Is there a reason behind this?

There's a million reasons why I would NOT use case sensativity when using a database, but I can't think of a single reason why they would. Would HIPAA have an impact?

Is there a way I can turn it off for just me in one of my settings or something? like you setting quote identifier?

thx!

 

[hwkranger]

I found the answer to my question. Would never have asked if I knew how to spell 'case-sensitive'

 

[gmmastros]

There are things you can do, but it's all ugly.

Basically, case sensitivity is controlled by the collation. Each database has a default collation. Also, each string column (char, varchar, nchar, nvarchar) has a collation. You can use a non-case sensitive collation when comparing/sorting data.


>> Is there a way I can turn it off for just me in one of my settings or something? like you setting quote identifier?

Nope. Sorry.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hwkranger]

I don't see a reason for using case sensativity.. and I found that I couldn't turn it off.

Thanks George.

 

[gmmastros]

You can temporarily turn it off by overriding the collation. This, of course, would have to be done on a query by query basis. (Hence the 'ugly' statement).

For example:

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]VarChar[/color](20) [COLOR=blue]Collate[/color] SQL_Latin1_General_Cp1_CS_AS)

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'George'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'GEORGE'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'george'[/color])

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Temp [COLOR=blue]Where[/color] Data = [COLOR=red]'George'[/color]

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Temp [COLOR=blue]Where[/color] Data [COLOR=blue]Collate[/color] SQL_Latin1_General_Cp1_CI_AS = [COLOR=red]'George'[/color]

SQL_Latin1_General_Cp1_CS_AS is a case sensitive collation. As such, the first query will only return 1 record, but the second query (where we temporarily override the collation) returns 3 records.

I hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

while there is no single option, I believe you can modify the individual columns to have a case insensitive collation.

of course this does mean going through all the text columns in all your tables, probably best done with a loop through sysobjects and information_schema.columns

--------------------
Procrastinate Now!

 

[hwkranger]

They use the Latin1_General_BIN collation here -- due to their FE app requiring it. I thought it was odd that an app would require case sensativity on the back end.

It's a 3rd party vendor product, which by all means is not an industry standard. (15% of the market uses it.)

It's something that I thought was odd... Guess I have to learn to deal with it.