Chapter II single-Table query T-SQL language Basics (3)

Single-table query (3) 2.6 Processing character data

Query processing of character data, including: types, collations, operators and functions, and pattern matching.

2.6.1 data types

SQL Server supports two character data types----plain and Unicode characters. The common character data types include char and varchar,unicode character data types including nchar and nvarchar.

The difference between ordinary and Unicode characters: ordinary characters use one byte to hold each character, while Unicode characters require two bytes.

Note: When representing a normal character constant, simply use single quotation marks: ' This is a regular charactor string literal '; When a character constant representing a Unicode type needs to be preceded by a char n as the prefix, n ' This is a regular charactor string literal '

Any data type with a name that does not contain a VAR element is fixed-length (Char,nchar), and for such a type, SQL Server reserves a fixed amount of space in the row for the column as defined by the column, so the length of the column is not the actual number of characters in the string---- (This type of storage consumption is not optimal and may cost more when reading data.)

The data type that contains the Var element in the name is variable-length (Varchar,nvarchar), and SQL Server saves the data at the actual length of the string, plus two additional bytes to hold the offset value of the data.----( This type of update data may need to be extended to the row, causing the data to move beyond the scope of the current page. The efficiency of updating data with variable-length data types is reduced compared to fixed-length data types.

Note: When defining a variable-length data type, you can use the max specifier without specifying the maximum number of characters. (When you define a column with the max specifier, you can save a value directly to the inside of the row, with a limit of 8000 bytes, by default. Values larger than the upper limit will be used as large objects (Lob,large object), which are stored outside of the row).

2.6.2 sorting Rules (Collation)

Chapter II single-Table query T-SQL language Basics (3)