Archive for May, 2009

Collation precedence conflict in SQL Server 2000

The collation precedence rules apply only to the character string data types, char, varchar, text, nchar, nvarchar, and ntext. Objects with other data types do not participate in collation evaluations.
link to article on msdn
A simple example is as follows

create table #TempRO
Id  int Identity (1,1),
App_No     NVarchar (100)
— insert some values in this table
Select *
from #TempRO T
Inner join App AO
On T.App_No = AO.App_No

We are having the App table using a different collation then the default that is “Arabic_CI_AS”

So the above select will give the following collation error, to remove it we just add the following statement while defining the temp table

create table #TempRO
Id  int Identity (1,1),
App_No     NVarchar (100)    collate Arabic_CI_AS

This not only happens in temp tables but also in the normal tables or at times if we are comparing columns so its better to use sp_help TableName or the syscolumns to find the collation of the two.

Happy Collating 😉


Read Full Post »