Collation Mismatch 1

[ousoonerjoe]

Simple question... I think.

I have a table that all the VARCHAR fields got converted to "SQL_EBCDIC037_CP1_CS_AS" whereas the rest of the system is using "SQL_Latin1_General_CP1_CI_AS" which is the system default. Don't ask how it happened, this is an inherited system. Five years later, we're still finding oddities as we clean this thing up. I suspect it's a leftover setting in TFS and will attack that next.

My question is this, are there any known issues switching the collation on the fly? Any potential conflicts?

Thank you.

SoonerJoe

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[gmmastros]

Why don't you just change the collation to whatever suits you.

If you do research regarding "change SQL server collation", you will get back a lot of scary things which primarily boils down to "reinstall your database engine". This advice is usually in response to changing the server's default collation.

Changing the collation on columns within a table is not at all a problem. Or, better put, no more problem than changing other attributes of a column.

If your table is relatively small (less then a couple hundred thousand rows), then you can simply to this:

Alter Table TABLE_NAME 
Alter Column COLUMN_NAME 
VarChar(10) Collate SQL_Latin1_General_CP1_CI_AS

Before running the code above, you'll want to make sure that the size and nullablility is correct.

If your table is large, you may run in to size and performance problems as noted here:

http://weblogs.sqlteam.com/dang/archive/2009/11/29/Collation-Hell-Part-3.aspx

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Thanks, George. I suspected a column level shift was pretty basic, but yeah, the searches returned some pretty drastic answers that I thought were rather over the top.

I appreciate the answer.



--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------