SQL Server的Collate语句需注意

汗，今天被Sql Server的Collate子句大玩了一把，看在线帮助不仔细！让自己绕了一个大圈，以后看MS帮助可要仔细了，事情是这样的：

下午，老大给我们发来一段SQL Script，要我们测试，看有没有错误，如有，请提出！整个Script全部在这里!

我当时就将这段脚本拉进了查询分析器，一执行，呵呵，根本没错啊！那老大为什么要发这样的邮件出来呢？于是我又切换了几个database,也没有什么问题，正当我准备测试完这一个database就放弃测试退出的时候，问题来了。错误消息如下：

Server: Msg 446, Level 16, State 9, Line 61

Cannot resolve collation conflict for equal to operation.

呵呵，有困难，找警察，咱有难，就找online啦。按下F1,键入collation,最后定位至See also中的Collate,查到帮助文件如下(不好意思，我只是将sql server2000 的在线帮助源封不动的复制了一下，当然在我当时没有看仔细的那一句我变换了颜色，各位朋友也请不要犯同样的错误为好。呵呵)：

COLLATE

A clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

Syntax

COLLATE < collation_name >

< collation_name > :: =

{ Windows_collation_name } | { SQL_collation_name }

Arguments

collation_name

Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified Windows_collation_name or a SQL_collation_name.

Windows_collation_name

Is the collation name for Windows collation. See Windows Collation Names.

SQL_collation_name

Is the collation name for a SQL collation. See SQL Collation Names.

Remarks

The COLLATE clause can be specified at several levels, including the following:

Creating or altering a database.

You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. You can also specify a collation when you create a database using SQL Server Enterprise Manager. If you do not specify a collation, the database is assigned the default collation of the SQL Server instance.

Creating or altering a table column.

You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Enterprise Manager. If you do not specify a collation, the column is assigned the default collation of the database.

You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

Casting the collation of an expression.

You can use the COLLATE clause to cast a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column. For the collation of an expression, see Collation Precedence.

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. For example, two tables with names differing only in case may be created in a database with case-sensitive collation, but may not be created in a database with case-insensitive collation.

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. The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the instance.

The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.

Collations are generally identified by a collation name. The exception is in Setup where you do not specify a collation name for Windows collations, but instead specify the collation designator, and then select check boxes to specify binary sorting or dictionary sorting that is either sensitive or insensitive to either case or aclearcase/" target="_blank" >ccents.

You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL collations:

SELECT *FROM ::fn_helpcollations()

SQL Server can support only code pages that are supported by the underlying operating system. When you perform an action that depends on collations, the SQL Server collation used by the referenced object must use a code page supported by the operating system running on the computer. These actions can include:

Specifying a default collation for a database when you create or alter the database.

Specifying a collation for a column when creating or altering a table.

When restoring or attaching a database, the default collation of the database and the collation of any char, varchar, and text columns or parameters in the database must be supported by the operating system.

Code page translations are supported for char and varchar data types, but not for text data type. Data loss during code page translations is not reported.

If the collation specified or the collation used by the referenced object, uses a code page not supported by Windows®, SQL Server issues error. For more information, see the Collations section in the SQL Server Architecture chapter of the SQL Server Books Online.

当时，我承认，我确实大致看完了全篇了，心里明白是排序规则的原因，导致了错误信息的出现。使用collate语句强制指定排序规则是可以解决的，于是我在老大的代码上的每个字串类型的字段后面都加上了 collate Chinese_PRC_CI_AS ，然后F5运行，faint...，问题照旧。于是改为：collate SQL_Latin1_General_CP1_CI_AS,嗯，问题解决，正当以为就这样可以解决的时候，我又试了一下没加之前没错的database,faint...,他们出现了同样的错误信息，难道是拆东墙补西墙。不行， 问题没有解决，于是，我也上QQ群发问了，也不知是因为今天是周末还是什么原因，总之没有一个人回答我。最后实在没有办法，只好自己再回来看上面那段其实我并不喜欢的帮助啦（因为是英文嘛！呵呵...）,当我看到

You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

着实把我喜了一把。马上改用collate database_default,嗯，一个通过、两个通过、三个通过....

OK,终于解决，松了一口气。

将这件事post上来，一是对自己作个警示：以后看帮助真的要仔细点。二是希望朋友不要犯类似的低级错误，以免浪费无谓的时间。如果要查看源码sql script,请点击这里下载。是提取database的属性的哦。

原文转自：http://www.ltesting.net