Unable to resolve the equal to Operation " chinese_prc_ci_as" and " sql_latin1_general_cp1_ci_as" A collation conflict between the.

What is a collation (collation)

About the SQL Server collation, it is estimated that everyone is not unfamiliar, when creating a database we often have to choose a collation (conllation), generally we will notice that each language collation has many kinds, such as the standard Continental Simplified Chinese chinese_ There are dozens of kinds of collations in PRC

What is the role of these collations? Let's take a look at MS's official explanation first:

A collation specifies the bit pattern that represents each character. It also specifies the rules for sorting and comparing characters. Collations have the following characteristics:

• Language
• Case sensitive
• Accent Sensitivity
• Distinguish Kana

For example, in SQL Server 2005, collation names are composed of two parts, such as Chinese_prc_ci_ai_ws
The first half refers to the set of characters supported by this collation, such as the collation of the CHINESE_PRC pointer to the continental simplified Unicode.
The second half of the suffix means the following:

_bin
Specifies that backward-compatible binary sort order is used.

_bin2
Specifies the binary sort order using the code-bit comparison semantics introduced in SQL Server 2005.

_stroke
Sort by stroke

_ci (CS)
is case-sensitive, CI not differentiated, CS differentiated

_ai (AS)
Whether accent-sensitive, AI is not differentiated, as differentiated

_ki (KS)
Whether the kana type is differentiated, Ki does not differentiate, KS differentiates

_wi (WS)
Whether the full half angle is distinguished, WI does not differentiate, WS differentiates

Since collations are so complex, it is not possible to equal operations such as union, Join, like, and so on by default between columns that have different collation rules, so there is a collation (collation) conflict.

Collation (collation) conflicts

We know that SQL Server supports multiple collations starting with 2000. A SQL Server 2000 database can use a collation other than the default collation. In addition, SQL Server 2000 supports specialized collation for columns.

This way, when we write a cross-table, cross-database, and cross-server operation of T-SQL, a collation violation occurs if the equal field collation is different.

For example, we first had two tables with the same structure, but the collation of the fields was different:

            --1. Create TableA.            CREATE TABLE Tagstablea            (                TagName        NVARCHAR ()    COLLATE chinese_prc_bin            )               --2. Create TableB.            CREATE TABLE Tagstableb            (                TagName        NVARCHAR (    COLLATE chinese_prc_ci_as            )   

When the table is built, execute:

            --3. Try to join them            

You will get a question similar to the following:

The collation violation between "Chinese_prc_bin" and "chinese_prc_ci_as" in the equal to operation cannot be resolved.

Common scenarios-temporary tables

We know that the temporary table for SQL Server is saved in the tempdb database. A database that uses temporary tables is not necessarily the same as the collation (conllation) of temporary tables. Therefore, collation violations occur when the collation of tempdb is not the same as the database collation that is currently using temporal tables.

In general, we may not notice collations when creating temporary tables, leaving the pitfalls of collation violations.

A stored procedure in the blog module of OpenLab V4.0, for example, has this vulnerability:

/****** to the Elephant ó: StoredProcedure [blogs].    [Up_creategettagids] Script Date: 01/20/2010 19:10:32 ******/set ansi_nulls ongoset quoted_identifier ONGO/*RETURN VALUES: ids*/--=============================================--Author: <lance zhang>--Create Date: &LT;2010-01-06&G t;--Description: <make sure all the tags EXISTS in DB, and then get their ids.>--1. Create Temp table.--2. Insert tagnames into Temp table.--3. ADD new Tags to [Categories] from query Temp table.--4. Batch Get All tags Ids from [categories].--5. Clear and Drop Temp table.--=============================================alter PROCEDURE [blogs]. [Up_creategettagids] (@BlogId INT, @TagNames XML) Asbegin/******************************* set CONFIG *************************************************/set NOCOUNT O N Set TRANSACTION Isolation level READ uncommitted SET numeric_roundabort OFF/******************************* DE CLARE VARIABLE ***********************//********************************begin transation***********************************            /begin TRY begin TRANSACTION; --1. Create Temp Table. CREATE TABLE#TagsTable(TagName NVARCHAR (+) )               --2.            Insert tagnames into Temp Table. INSERT into #TagsTable SELECT TG. Tags.value (' @i ', ' NVARCHAR ') as TagName from @TagNames. Nodes ('/ts/t ') TG (Tags)- R.            ADD new Tags to [Categories] from query Temp Table.                    BEGIN INSERT into [Categories] ([BlogId] , [ParentID], [CategoryType], [CategoryName], [Loweredcateg                    Oryname], [Slug], [Loweredslug], [Description]                , [CREATEDDATEUTC], [totalentities], [SortOrder], [state] ) SELECT @BlogId, 0,--Pa                    Rentid, 0 as default.               2,                     --CategoryType, 2 as Post Tag. Tt. TagName, LOWER (TT. TagName), TT.                    TagName,--Slug, use CategoryName as default. LOWER (TT.                    TagName),--loweredslug, use Loweredcategoryname as default.                    ",--Description, Empty as default.                    getUTCDate (), 0,--totalentities, 0 as default.                    1,--SortOrder of posttags can always be 1.                1--state, 1 as Normal. From #TagsTable TT WHERE LOWER (TT.                         TagName) not in (SELECT C.[loweredcategoryname] From [CAtegories] C with (UPDLOCK, HOLDLOCK) WHERE [BlogId] = @BlogId                        and [CategoryType] = 2--Post Tag. ) END--4.            Batch Get All tags Ids from [Categories]. BEGIN SELECT [CategoryId] from [Categories] C with (NOL ock) JOIN #TagsTable TT on c.[loweredcategoryname] = LOWER (TT.                TagName) WHERE C.[blogid] = @BlogId and C.[categorytype] = 2                    --Post Tag.            and c.[state] = 1--1 as Normal status. END--5.            Clear and Drop Temp Table.        TRUNCATE table #TagsTable DROP table #TagsTable COMMIT TRANSACTION; RETURN 1 END TRY BEGIN CAtch IF xact_state () <> 0 BEGIN ROLLBACK TRANSACTION; RETURN-1 End End Catchendgo

Common Solutions

Knowing what a collation conflict is, we will then analyze the conflicting solution, taking the database-level collation as an example, in general, the solution has the following

1. Delete the SQL instance and rebuild--in most cases it doesn't say-_-| | |
2. To modify the collation of a database--refer to this article by Brother Aniu
3. Use collate database_default in T-SQL to resolve conflicts--the next major discussion of this

COLLATE Database_default

Collate The xxx action can be used when the field is defined or used, and it will define or convert the field to the collation format of XXX . Instead, Collate database_default resolves the conflict by defining or converting the field to the default collation of the current database .

For example, in the following code, Collate database_default is used to resolve the collation violation of the field in the equal operation:

        Insert into Security.report (Name)              Select C.path from SSRS. Catalog C         database_default like @ReportPath + '/% '               database_default not In (Select Name from Security.report R)        

Of course, if you add Collate database_default to the field definition when creating a temporary table, you can easily resolve potential collation conflicts, such as the stored procedure mentioned in the previous section, as long as you make the following modifications.

            --1. Create Temp Table.            CREATE TABLE #TagsTable            (                TagName        NVARCHAR ()    COLLATE database_default            )   

Concluding remarks

For professional Sqler, there are many application scenarios for collations, such as the use of collation features to calculate Chinese character strokes and get pinyin initials, and more, see the MSDN documentation: http://msdn.microsoft.com/zh-cn/library/ aa258237 (en-us,sql.80). aspx

Reference: http://www.cnblogs.com/blodfox777/archive/2010/01/21/sqlserver-collation-conflict-and-solutions.html