Case sensitive sql-2005?

[jsteph]

Hi,
Please excuse what may be dumb question...we just had a new sql server 2005 machine installed, and the hardware company did the install of the box and the software.

It seems they've set it up as case-insenstive. The collating order is:
SQL_Latin1_General_CP850_BIN2

Is this the value that directs the case-sensitivity, and if so, what collation does it need to be changed to get the db to be case-INsensitive? With the assumption that this is English, USA, standard vanilla database? Our original db was SQL_Latin1_General_CP1_CI_AS

I've never known anything at all about all the different collating values and this is the first time I've run into this, and I simply have no idea what the norm is. Also--is this specific to the db instance and not individual dbs?
Thanks very much,
--Jim

 

[SQLBill]

There is a server collation, but individual databases, tables, and/or columns can have their collation set separately.

BEFORE you make any change, check with the vendor. Some vendor products MUST use a specific collation. Changing it can invalidate any support agreement and may cause the application to fail.

Use the index tab in the Books OnLine and enter COLLATION.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

OH.... collations can be fun. I agree with SQLBill. You should look at Books On Line for information about collations.

There's a couple of gotcha's that I'll warn you about.

There is a default collation for the server. Each database has a default collation, and each column has a collation. If you truly do want to change the collation, I recommend you change it everywhere.

Also... if you are using temp tables AND joining to regular tables on a char/varchar column, then it is important for your TempDB to have the same collation as your database.

If your application vendor requires a particular collation that does not suit you, you may be better off creating a named instance. It sounds like a lot, but trust me when I say that you are better off having all the collations in all the databases and for all the columns to match.

-George

"the screen with the little boxes in the window." - Moron

 

[jsteph]

Bill, George,
Thank you both, I'm going to standardize it all to the way it was before, in the old box.

The backstory is that we had a piece of software that, it appears, needed that particular collation (SAP Xi), but we cancelled the xi project (massive overkill for the task at hand), and I was left with a fairly powerful server with sql-2005 installed and ready to go, so I commandeered it for another project, quickly finding the collation issue. I guess it's a simple fix, but I will make sure all db's on this have the same collation, I can see why things like a join on a char/varchar field would be an issue, not to mention making sure all tsql, sql, etc matches the case of the table, field, sp, etc.
--Jim