When collations collide?

by Michael S. Kaplan, published on 2006/10/27 03:03 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2006/10/27/878277.aspx


Praveen asks in one of the SQL Server aliases in Microsoft a question about an issue that us not very well understood:

Hi

Let me know if this is not a appropriate question for this DL.

My Sql Server (2005) has case sensitivity turned on. I create a database with case sensitive option turned off (SQL_Latin1_General_CP1_CI_AI), create a table and create a stored procedure. The creation of stored procedure gives me a error indicating that case sensitivity does not work for the datatype in variables. Is this true? Is there some other option I can specify during the create database statement so that this will work too?

Thanks
Praveen

CREATE DATABASE  Test1 COLLATE SQL_Latin1_General_CP1_CI_AI
go
use Test1
go
CREATE TABLE table1(        Column1         nvarchar(64) NOT NULL  )
Go

create proc proc1
    @Column1 nvarchar(64)
as
begin
    if exists (select * from table1               where
               @column1 = Column1)
    begin
        return -1
    end
    return 0
end

Error message during the sproc creation:

sp_helpdb Test1Msg 137, Level 15, State 2, Procedure proc1, Line 8
Must declare the scalar variable "@column1".

Bart was very quick to point out where the issue is documented:

See the “Identifier Collation” topic in BOL:

The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database. Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database and then referenced when the context has been switched to another database. Therefore, the identifiers for variables, GOTO labels, and temporary tables are in the default collation of the instance.

The topic that Bart refers to is right here and has links to several related topics about collation at various levels. None of them really talk about the additional issues related to security, given the 2-3 different possible login logics for comparing user names (server collation, local machine using the NT object namespace, or domain login usually but not always using Active Directory).

But I thought Praveen's question might get the ball rolling here, with what may be one of the easier scenarios to understand after it is explained. Future topics may delve more into the ones that actually are just as hard to fathom after you understand them as they are before. :-)

 

This post brought to you by (U+0e01, a.k.a. THAI CHARACTER KO KAI)


# Dean Harding on 27 Oct 2006 3:47 AM:

Yeah, this is a real problem when you're developing an application that can be installed on somebody else's instance of SQL Server.

You basically HAVE to develop it on a case-sensitive instance locally, otherwise one of your users invariably has a case-sensitive instance installed and your app breaks. They get upset if you tell them to install it on a new instance that is not case-sensitive :)

# John Ingres on 27 Oct 2006 9:55 AM:

We've been looking at the implications of moving our database from

SQL_Latin1_General_CP1_xxxx

to

Latin1_General_xxx

since it is the recommended pratice to use Windows collations instead of the legacy SQL collation but this is a large change affecting more than 30 applications and over a hundred production systems and I have been investigating differences between those two collations and information is extremely scarce. Is there a source of information with detailed information? Of course, we will test exhaustively but subtle differences in sort order for example are not always easily apparent.

thanks

John

# Michael S. Kaplan on 27 Oct 2006 5:08 PM:

Hi John and Dean,

Both of you touch on issues that deserve their very own posts. Sty tuned this weekend and I will see what I can do....


referenced by

2006/10/30 If you are more sensitive, you'll pick up on more problems

2006/10/29 SQL Server: compatibility collations vs. Window collations

go to newer or older post, or back to index or month or day