What does collate chinese_prc_ci_as null mean?

When creating table, we often encounter such a statement, for example, corpname nvarchar (10) Collate chinese_prc_ci_as null. What does it mean?

Take a look at the following:

 

Syntax

Collate <collation_name>

<Collation_name >::=
{Windows_collation_name} | {SQL _collation_name}

Parameters

Collation_name

Is the name of the sorting rule applied to expressions, column definitions, or database definitions. Collation_name can only be the specified windows_collation_name or SQL _collation_name.

Windows_collation_name

Is the name of a Windows sorting rule.

SQL _collation_name

Is the name of the SQL sorting rule.

Note

The collate clause can be specified at the following levels:

Create or change a database.
You can use the create database or alter database statement's collate clause to specify the default database sorting rules. You can also use the SQL Server Enterprise Manager to specify sorting rules when creating a database. If no sorting rule is specified, the default sorting rule for the SQL server instance is assigned to the database.

Create or modify a table column.
You can use the create table or alter table statement's collate clause to specify the sorting rules for each string column. You can also use the SQL Server Enterprise Manager to specify sorting rules when creating tables. If no sorting rule is specified, the default database sorting rule is assigned to the column.

You can also use the database_default option in the Collate clause to specify the default sorting rules for the columns in the temporary table to connect to the current user database (instead of tempdb.

Sorting rules of projection expressions.
You can use the Collate clause to project character expressions to specific sorting rules. The default sorting rules of the current database will be assigned for the character nominal value and variable. A sorting rule is defined for the specified column for the column reference.

The sorting rule of the identifier depends on the level at which the identifier is defined. Assigns default sorting rules for instances for instance-level objects (such as login names and database names) identifiers. Assign default database sorting rules for the identifiers of database objects (such as tables, views, and column names. For example, for tables whose names are case-insensitive, you can create a table in a database that uses case-insensitive sorting rules, but not in a database that uses case-insensitive sorting rules.

When the connection context is related to the database, you can create variables, Goto labels, temporary stored procedures, and temporary tables, and reference them when the context is switched to another database. The variables, Goto labels, temporary stored procedures, and temporary table identifiers are in the default sorting rules of the instance.

The collate clause can only be applied to Char, varchar, text, nchar, nvarchar, and ntext data types.

The following describes the sorting rules:

What is a sorting rule? In Microsoft SQL Server 2000, the physical storage of strings is controlled by sorting rules. The sorting rule specifies the bit mode of each character and the rules used to store and compare characters. "

A sorting rule is generally identified by its name. The exception is that the name of the sorting rule is not specified for the Windows sorting rule during installation, but the sorting rule indicator is specified, then select the check box to specify binary or dictionary sorting for case-insensitive and accent-insensitive.

You can run the fn_helpcollations function to retrieve the list of all valid sorting rule names for Windows and SQL sorting rules:

Select * From: fn_helpcollations ()

SQL Server only supports the code pages supported by the basic operating system. When you perform an operation that depends on a sorting rule, the SQL Server sorting rule used by the referenced object must use the code page supported by the operating system running on the computer. These operations can include:

<! -- [If! Supportlists] --> · <! -- [Endif] --> when creating or changing a database, specify a default sorting rule for the database.

<! -- [If! Supportlists] --> · <! -- [Endif] --> when creating or changing a table, specify a default sorting rule for the column.

<! -- [If! Supportlists] --> · <! -- [Endif] --> when restoring or attaching a database, the operating system must support the default database sorting rules and any char, varchar, and text columns or parameters in the database.

Supports code page conversion for char and varchar data types, but does not support code page conversion for text data types. Data loss during code page conversion is not reported.

If the referenced object uses or the specified sorting rule uses a code page not supported by windows, SQL Server issues an error message. For more information, see the "sorting rules" section in the "SQL Server architecture" chapter of SQL Server books online.

The name of a sorting rule consists of two parts. The first half is the character set supported by this sorting rule.

For example: chinese_prc_cs_ai_ws

First half: the Unicode character set. The chinese_prc _ pointer sorts Unicode in simplified Chinese characters.

The second half of the sorting rule is the suffix meaning:

_ Bin binary sorting

_ Ci (CS) is case sensitive, CI is case insensitive, and CS is case sensitive

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

_ KI (KS) indicates whether Kana is distinguished. Ki is not distinguished, and KS is distinguished.

_ Whether wi (WS) is differentiated by width WI and WS

Case Sensitive: select this option if you want to make the comparison between uppercase and lowercase letters different.
Accent differentiation: select this option if you want to treat the comparison as different from the accent and non-accent letters. If this option is selected, the comparison also treats letters with different accents as unequal.

Kana differentiation: select this option if you want to treat Katakana and katakana as different Japanese syllables.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/aspbxw/archive/2009/03/10/3975498.aspx