SQL Server的Collate语句需注意

核心提示： The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such as logins and database na

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 accents.

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