SQL Server's collate statement needs to be noted

Source: Internet
Author: User
Tags goto

Khan, today by the SQL Server COLLATE clause big play a, watch online help not careful! Let yourself around a large circle, later look at Ms Help to be careful, the thing is this:

Afternoon, the eldest brother sent us a section of SQL Script, we want to test, see if there is no error, if so, please propose! The whole script is all here!

I then pulled this script into the Query Analyzer, a implementation, hehe, absolutely right AH! Why would the boss send such an e-mail? So I switched a few database, there is no problem, just when I am ready to test this database to give up the test exit, the problem comes. The error message is as follows:

Server:msg 446, level, State 9, line 61

Cannot resolve collation conflict for equal to operation.

Oh, there are difficulties, to find the police, we have difficulty, look for online. Press F1, type collation, and finally navigate to the collate in also and find the Help file as follows (sorry, I just copied the SQL server2000 online Help source, of course, I did not see the careful sentence I changed the color, Dear friends, please do not make the same mistake as well. hehe):

COLLATE

A clause that can is applied to a database definition or a column definition to define the collation, or to a character St Ring 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 being 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. The Windows collation Names.

Sql_collation_name

is the collation name for a SQL collation. Collation Names SQL.

Remarks

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

Creating or altering a database.

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 if you create a database using SQL Server Enterprise Manager. If you don't specify a collation, the database is assigned the default collation of the SQL Server instance.

Creating or altering a table column.

Can specify collations for each character string column using the COLLATE clause of the CREATE table or ALTER table St Atement. Can also specify a collation when you create a table using SQL Server Enterprise Manager. If you don't specify a collation, the column is assigned the default collation of the database.

You can also with the Database_default option in the COLLATE clause to specify this a column in a temporary table use the C Ollation 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 expression, collation precedence.

The collation of an identifier depends in which it is defined. Identifiers of Instance-level objects, such as logins and database names, are assigned the default collation of the Instan Ce. Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of T He database. For example, two tables with names differing-in-case may-created in a-database with case-sensitive collation, but May is created in a database with case-insensitive collation.

Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection as sociated with one database, and then referenced then 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.