Shulou(Shulou.com)05/31 Report--

This article to share with you is about SQL Server sorting rules, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

I. Summary

1. The collation in SQL Server is the character set encoding in other relational databases;

Collation rules in SQL Server can be set in 3 places, as follows:

Server level (instance): instances ----> Settings when installing database

Database level: database

Column level: columns

Therefore, when using SQL Server's collation rules, you only need to ensure that these three are consistent, which is the correct way to use them;

3. SQL Server sorting rules not only affect the sort order of record lines, but also affect whether Chinese display is garbled;

4. When creating the database, if we do not specify a collation, the database will use the instance default collation;

SQL Server collations only affect character-type columns, such as char, varchar, text, nchar, nvarchar, ntext, so the collation of non-character-type fields in the query view sys.columns shows NULL;

6. It should be noted that although the collation of the database can be changed, it is problematic, because even if the collation of the database is changed, the collation of the fields in the database may still be the original, and there is no change, which may be problematic when used, so the collation of the database tries not to change at will.

7. Binary sorting is the fastest of all collations because SQL Server uses fast, simple sorting algorithms without any adjustments.

II. Query statements

1. Query character set encoding

Command: SELECT COLLATIONPROPERTY('Chinese_PRC_Stroke_CI_AI_KS_WS', 'CodePage')

Note:

（1）. The collation for this database instance is Chinese_PRC_CI_AS

（2）. Find out the character set code corresponding to the result

936: Simplified Chinese GBK

950: Traditional Chinese BIG5

437: American/Canadian English

932: Japanese

Korean

866: Russian

65001 ：unicode UTF-8

2. View the collation of instances

Command: select serverproperty(N'Collation')

3. View the collation of all databases under the instance

Command: select name, collection_name from sys.databases

4. Modify the collation of an existing database

Command: alter database telno collate Chinese_PRC_BIN

Note: It is not recommended to use, even if the database is changed, but the columns of the table in the library are still the original.

5. Query the collation of columns

Command: select name, collection_name from telno.sys.columns where collection_name is not null

Note: The collation for non-character fields is NULL, so filter out NULL results.

6. View collations supported by the current version of SQL Server

Command:

select * from ::fn_helpcollations()

select * from fn_helpcollations()

Note:

(1) Double colon is the usage of calling built-in functions in SQL2000, SQL2005 and above do not need to use colon can also use built-in functions;

(2) Detailed explanation of the meaning represented by the sorting rules

Chinese_PRC_: refers to the collation rules for the Unicode character set of simplified Chinese characters

Meaning of the second half:

_BIN: Binary sort

C : case, upper case;

A: accent;

I: Insensitive, insensitive, indistinguishable;

S: sensitive, sensitive, distinguishing;

W: width, width

K: kanatype, pseudonym

eg：

_CI: Case insensitive

_AS: Distinguish stress

The above is what the sorting rules in SQL Server are, Xiaobian believes that some knowledge points may be what we see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.