SQL SERVER---collation, data type

First, sorting rules

Sometimes when we insert text into the database, there is a garbled "?", it is possible that we created the database is not set up collation

Taking Chinese_prc_ci_as as an example

The first half of CHINESE_PRC refers to the collation of Unicode for Simplified Chinese characters

The meaning of the latter half is:

_bin binary Ordering

_ci (CS): Case sensitive, CI not differentiated, CS differentiated

_ai (AS): whether accent-sensitive, AI-insensitive, as-differentiated

What is accent-sensitive, here is mainly Western writing, for example, "a" is not equivalent to "á".
If you do not select this option, SQL Server considers the accent to be equivalent to the corresponding non-accented letter

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

Used to specify the order of kana in Japanese, we often use ABC to describe the most basic knowledge of things, the Japanese also used "いろは" to express the same meaning

_wi (WS): Whether width is differentiated, ki is not differentiated, KS differentiates (compare half-width character and full-width character view)

How do I modify the database collation?

To modify a database:

ALTER Database
COLLATE chinese_prc_cs_as

To modify a column:

ALTER TABLE TB
ALTER COLUMN colname nvarchar (+) COLLATE chinese_prc_ci_as

How to view the collation of a table

Select collation from xxx
where id=object_id (N ' yourtablename ')

Reference: Https://technet.microsoft.com/zh-cn/library/ms188046.aspx

So what does collation do in SQL queries?

1. You can have content sorted by pinyin:

such as SELECT * from XX ORDER by name collate Chinese_prc_cs_as_ks_ws

2. You can have the contents of a column sorted by last name stroke:

such as SELECT * from #t ORDER by name collate Chinese_prc_stroke_cs_as_ks_ws

Resolving collation conflicts for equal to operations

A sort conflict occurs when two different collation link queries, just after the query with the uniform collation

Collate Chinese_prc_ci_ai_ws

Ii. Types of data

Find out what the SQL data types are, the difference between them:

Character string:

Char: A fixed-length string. A maximum of 8,000 characters.

Text: A variable-length string. Up to 2GB character data.

VARCHAR (n): A variable-length string. A maximum of 8,000 characters.

Unicode string:

NCHAR (n): fixed-length Unicode data. Maximum of 4,000 characters

NTEXT: variable-length Unicode data. Up to 2GB character data

nvarchar (n) variable-length Unicode data with a maximum of 4,000 characters.

nvarchar (MAX): variable-length Unicode data. A maximum of 536,870,912 characters.

Binary type:

Binary: fixed-length binary data. Up to 8,000 bytes.

Bit: Allow 0, 1, or NULL

Image: Variable-length binary data. Up to 2GB

varbinary (50) variable-length binary data. Up to 8,000 bytes.

varbinary (MAX) variable-length binary data. Up to 2GB bytes

Number type:

Bigint:8 bytes between 9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

Decimal (p,s) 5-17 bytes:

The P parameter indicates the maximum number of digits that can be stored (left and right of the decimal point). P must be a value between 1 and 38. The default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value between 0 and P. The default is 0.

Float (n): 4 or 8 bytes floating Precision numeric data

The parameter n indicates whether the field holds 4 bytes or 8 bytes. Float (24) holds 4 bytes, while float (53) holds 8 bytes. The default value for N is 53.

The Int:4 byte allows all numbers from 2,147,483,648 to 2,147,483,647.

Tinyint 1 bytes allow all numbers from 0 to 255.

The Money:8 byte is a currency data between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.

The smallmoney byte is a currency data between -214,748.3648 and 214,748.3647.

Real:4 bytes Floating Precision numeric data from -3.40e + 38 to 3.40E + 38.

SmallInt 2 bytes Allow all numbers from-32,768 to 32,767.

Date Type:

Datetime:8 bytes from January 1, 1753 to December 31, 9999 with a precision of 3.33 milliseconds

Smalldatetime:4 bytes from January 1, 1900 to June 6, 2079, with a precision of 1 minutes.

Timestamp: Stores a unique number that is updated whenever a row is created or modified. The timestamp is based on the internal clock and does not correspond to real time. There can be only one timestamp variable per table
Other data types:

Sql_variant stores data of up to 8,000 bytes of different data types, in addition to text, ntext, and timestamp.

uniqueidentifier store Global identifiers (GUIDs)

XML to store XML formatted data. Up to 2GB.

SQL SERVER---collation, data type