When collations collide?

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)