how to write collation agnostic queries?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » how to write collation agnostic queries?

how to write collation agnostic queries?

Rate Topic

Display Mode

Topic Options

Author

Message

Abhishek Jain-593926

Posted Monday, December 31, 2007 5:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 13, 2009 3:37 AM
Points: 6, Visits: 16

Hi All,

A very warm happy new year to all of you!

I have one question. I am writing a desktop application that was targeted to default SQL_Latin1_General_CP1_CI_AS collation. System fails to work if database is installed in any other server with a case sensitive collation say french, because field casing in queries does not match with field cases in database. Moreever there are issues with joins in tables having diffrent collations.

Please note that I am installing my tables in a third party ERP database so I cannot control database collation. I can control collation of my tables but then there are issues when there is join between my tables and target database's tables

Any ideas how to overcome this problem. Any help and best practices would be highly apprecaited.

warm regards,
Abhishek Jain
jain_abhishek_77@yahoo.co.in

Post #437525

colin.Leversuch-Roberts

Posted Monday, December 31, 2007 9:52 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

it's very important that when you create objects you don't include collate statements unless there is a precise reason to do so, that way your objects pick up the database collation, having objects of mixed collations in the same database is a real issue. To avoid issues with multiple database collations generally I've found using " COLLATE database_default " usually does the trick.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #437596

Abhishek Jain-593926

Posted Wednesday, January 02, 2008 4:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 13, 2009 3:37 AM
Points: 6, Visits: 16

Thanks,

I am already doing it. Still my queries fail in because they are written in case insensitive way. I would like to know how other people are doing it. One option is to take care of cases when you write your queries. There are issues when you compare data values. Like 'JOHN' and 'john' are different. Is there any option to coerce collations at connection level?

thanks,
Abhishek

Post #437910

Lowell

Posted Wednesday, January 02, 2008 10:48 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

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
SELECT TOP 5
  'ALTER TABLE ' + sysobjects.name + ' ALTER COLUMN ' + syscolumns.name + ' ' 
  + TYPE_NAME(SYSCOLUMNS.XTYPE)  
  + ' 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 ('text','ntext') 
    ORDER BY TBLNAME,COLNAME

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #438068

Jeremy-475548

Posted Wednesday, January 02, 2008 10:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 23, 2012 7:57 PM
Points: 168, Visits: 789

A very warm New Year to you too (and hopefully not because of global warming :)
Are you asking to do something like this?

Select * from
table1 join table2 on whatever
Where LCASE(table1.LastName) = LCASE(table2.LastName)?
This would be "connection specific".

Post #438073

Jeremy-475548

Posted Wednesday, January 02, 2008 11:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 23, 2012 7:57 PM
Points: 168, Visits: 789

Another thought here...
you can "cast" a column to a particular collation at select time.
For Example

Select distinct LastName COLLATE SQL_Latin1_General_CP1_CI_AS
From testtable

If you had a case sensitive column, you can use the COLLATE clause to force the collation you want at run-time without schema changes.
There may be some gotchas that I don't know about with this method.

Post #438075

colin.Leversuch-Roberts

Posted Wednesday, January 02, 2008 3:16 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

ah case sensitivity as well - I covered this on a presentation I did but ti's not really something I can drop into a post and I'd need to do some more testing - in the instance you talk about with JOHN and john then you must force both sides to a case insensitive collation or force the arguments to the same case, by using upper() for instance. It's a real pain isn't it?

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #438169

Abhishek Jain-593926

Posted Friday, January 04, 2008 12:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 13, 2009 3:37 AM
Points: 6, Visits: 16

Dear All,

Thank you very much for your expert views on my problem.

As I develop ERP products that target multiple countries I wanted a generic solution to my problem. Here is what I learned and recapitulate for your expert advice

1. When installing your database in thrird party database or when creating a database that would interact with a third party database (say SAP Busineses One, Dynamics) keep collation of your database similar to third party database
2. When creating temporary tables keep the collation to database default (by default temporary tables have collation of tempdb database)
3. Write your sql queries keeping in mind that they can be case sensitive (in case sensitive database table authors is different from table Authors). Preferably use a query generator tool whereever possible.
4. Avoid mix casing when creating tables, columns, sp, triggers etc. Use lower case or upper case but not both. Like table authors can be created as
CREATE TABLE AUTHORS
AS
[AU_ID] ID NOT NULL,
[AU_LNAME] NVARCHAR (40)....
5. Always use unicode types NVARCHAR, NCHAR etc
6. Avoid conversion of string to dates. For example following query
SELECT * FROM SALES WHERE SALE_DATE>'15-May-2006' would definitely fail in a german collation. Always use parameterized queries like
SELECT * FROM SALES WHERE SALE_DATE>@adate. Similarly do not write logic based on named date parts, for example MONTH(@adate) would return MAY in english amd MAI in german.
7. As most business applications are witten in programming languages like vb, .NET, JAVA it is imperative that code is langauge agnostic and is not compiled against any specific locale.

This list is by no means exhaustive and need valauable suggestions by all experts. I request all of you and moderators of this forum to come forward and create a guideline for creating globalized sofwares using sql server

thanks,
Abhishek Jain

Post #438759

« Prev Topic | Next Topic »

Permissions