RE: how to write collation agnostic queries?

SSC Guru

Points: 323504

January 2, 2008 at 10:48 am

you can't set a session/connection to use a certain type of collation...it's all in the database.

we run into this occasionally, where a client will have a default collation of case sensitive,and then they create a database...which our program assumed to be case insensitive.

In our case, we make the client run a script to change the db to case insensitive, and then change every single column of type text/ntext/char/varchar etc. to the proper collation as well.

In reality, we have an option in our program do it...it builds the statments and runs them. Note it's just doing TOP 5, this can be a BIG perforamnce hit if you have tens of thousnads of columns.

here's an example: this is for sql2000, you might need to tweek this for varchar(max) :

DECLARE @collname varchar(128)

SET @collname='SQL_Latin1_General_CP1_CI_AS'

--change the db

SELECT 'ALTER DATABASE ' + db_name() + ' COLLATE ' + @collname

--change the defined length columns

SELECT TOP 5

'ALTER TABLE ' + sysobjects.name + ' ALTER COLUMN ' + syscolumns.name + ' '

+ TYPE_NAME(syscolumns.xtype) + '(' + CONVERT(VARCHAR,syscolumns.length) + ') '

+ ' COLLATE ' + @collname

+ CASE isnullable WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,

sysobjects.name AS TBLNAME,

syscolumns.name AS COLNAME,

TYPE_NAME(syscolumns.xtype) AS DATATYPE,

syscolumns.length as length

FROM sysobjects

INNER JOIN syscolumns ON sysobjects.id=syscolumns.id

WHERE sysobjects.xtype='U'

AND TYPE_NAME(syscolumns.xtype) IN ('char','varchar', 'nchar','nvarchar')

ORDER BY TBLNAME,COLNAME

--change the text columns