Shulou(Shulou.com)06/01 Report--

Originally good, the computer reinstalled unexpectedly encountered this problem. Baidu knew the reason after. Write it down.

It was because the sorting rules of the two table fields were different, so it took a long time to find out that it was caused by the temporary table that was created.

Solutions are as follows:

CREATE TABLE # Item (

Id INT PRIMARY KEY

Item_id varchar (30) Collate Database_Default

Item_name varchar (50) Collate Database_Default

Seq varchar (10) Collate Database_Default

Price money

Cost_price money

[path] varchar Collate Database_Default

Date_deleted datetime

)

Collate Database_Default is added to refer to the default collation of the database.

By the way, I learned:

What is a collation? MS describes it like this: "in Microsoft SQL Server 2000

The physical storage of strings is controlled by collation. The collation specifies the bit pattern that represents each character and the memory

Rules used to store and compare characters. "

Execute the following statement within the query analyzer to get all the collations supported by SQL SERVER.

Select * from:: fn_helpcollations ()

The collation name consists of two parts, and the first half refers to the character set supported by this collation.

Such as:

Chinese_PRC_CS_AI_WS

The first half: refers to the UNICODE character set, and Chinese_PRC_ refers to the sorting rules for mainland simplified characters UNICODE.

The second half of the collation is the suffix meaning:

_ BIN binary sort

Whether CI (CS) is case-sensitive, CI is not case-sensitive, CS is case-sensitive

Whether _ AI (AS) distinguishes stress, AI does not distinguish, AS distinguishes

Whether _ KI (KS) distinguishes katakana types, KI does not distinguish, KS distinguishes

Whether _ WI (WS) distinguishes width WI does not distinguish, WS distinguishes

Case sensitive: select this option if you want the comparison to treat uppercase and lowercase letters as unequal.

Distinguish stress: select this option if you want the comparison to treat accented and unstressed letters as unequal. If you select this option, the comparison also treats letters with different stresses as unequal.

Distinguish katakana: select this option if you want the comparison to treat katakana and katakana Japanese syllables as unequal.

Distinguish width: select this option if you want the comparison to treat half-width characters and full-width characters as unequal

Other solutions:

1. Change the fields of one table in the database to the same collation as the fields to be compared in another table

2. Add a SQL fragment like this after the Where clause of the SQL statement: collate Chinese_PRC_CI_AI_WS

Such as: Select A.* From A Magi B Where A.a = B.b collate Chinese_PRC_CI_AI_WS

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.