ansaurus

Question

SQL Server 2005 collation issue

Answer 1

+5 A:

You can change columns collation on the fly if you need to.

E.g.

select table1column1 collate database default  + table2column2 collate database default from ...

"Database default" could be whatever the collation you are wanting to use.

You can alter the collation of a column permanently with

ALTER TABLE ... ALTER COLUMN Table1Column1
            varchar(50) COLLATE Latin1_General_CI_AS NOT NULL
GO

u07ch 2009-05-10 08:01:05

Cool, a quick question, why SQL Server does not allow concatenate two columns from tables with different collation? What is the internal root cause?

George2 2009-05-10 08:05:41

The internal cause is that the two strings with different collations are essentially incompatible. Every string must have exactly one collation, but SQL Server cannot simply guess which collation the result should have, so you must specify it.

Tomalak 2009-05-10 08:32:17

@Tomalak, I am new to SQL collation and could you recommend me some documents to read for collation beginner?

George2 2009-05-10 08:35:31

Well, there is the obvious: http://en.wikipedia.org/wiki/Collation, and going from there I recommend reading through the SQL Server 2005 Books Online on this topic: http://msdn.microsoft.com/en-us/library/ms143503.aspx. I suppose there is a lot more to find on the net.

Tomalak 2009-05-10 08:46:01

Cool, the last question, why do you say "two strings with different collations are essentially incompatible" and why "Every string must have exactly one collation"? Could you show some more insights about how you made such conclusion please?

George2 2009-05-10 09:01:52

Simply put, a collation is to a string like a language is to a word. Every word is exactly in one language. You cannot concatenate two words in different languages in any meaningful way, you'd have to translate one of the words first so that their languages match.

Tomalak 2009-05-10 09:08:01

Cool, Tomalak! I am confused that I did not find any ways to know the collation for a specific column? Any solutions?

George2 2009-05-10 09:13:06

Right click the column in SSMS, and it will show the collation as one of it's properties. 99% of columns have the same collation as their database, in the general tab of database properties.

Andomar 2009-05-10 10:11:10

Cool Andomar, I think this question is answered. I have another related question to discuss the root cause of collation conflicting other than how to resolve conflicting from this thread. It is appreciated if you could discuss further and share more of your points. :-)The new thread is here,http://stackoverflow.com/questions/845321/join-column-with-different-collation-issue

George2 2009-05-10 13:17:25

ansaurus

tags:

views:

answers:

SQL Server 2005 collation issue

related questions