Transcript Unicode and Collation Support in Microsoft SQL Server

Unicode and Collation Support
in Microsoft SQL Server
Michael S. Kaplan
Software Design Engineer
Trigeminal Software, Inc.
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
Unicode Support

Uses the "N" or national data types from the
SQL-92 specification
 NCHAR, NVARCHAR, NTEXT
 What the SQL-99 spec says about Unicode
 Interoperability with other clients
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
Collation in SQL Server <= 6.5

No Unicode support at all
 One code page per server
 One collation per server
 No good solution for multilingual support
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
Collation in SQL Server 7.0

Unicode datatypes supported
 Two collations
– Unicode
– Non-Unicode

Number of collations distilled down to the
minimum necessary
 Collation independent of operating system
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
Collation in SQL Server 2000

Combined code pages and collations into a
single entity
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
"Windows" collations

43 language collations
– Added for unique code pages
– Added for unique ordering

Suffix meanings
– _BIN (Binary)
– _CI/_CS (Case sensitivity)
– _AI/_AS (Accent sensitivity)
– _KS - kanatype sensitivity (hiragana/katakana)
– _WS - width sensitivity (full/half width)
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
SQL Collations

Provided for backwards compatibility with
prior versions of SQL Server
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
Collation at four levels

Server
 Database
 Column
 Expression
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
At the server level

Acts as a default for all databases
 Can be changed with RebuildM.exe in the
tools\BINN dir
 Querying the server collation:
SELECT CONVERT(char, SERVERPROPERTY('collation'))
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
At the database level

Every database has a collation (default is
the server collation)
 Collation can be changed under some
circumstances
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
At the column level

Overrides database level collation
 Specifies code page for non-Unicode
columns
 Again, can be changed under some
circumstances
 No multilingual columns with separate
collations
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
At the expression level

Can be used to override any other collation
 uses the COLLATE keyword
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
More on the COLLATE keyword
COLLATE [<Windows_Collation_name>|<SQL_Collation_Name]

Specific rules of precedence:
– Explicit (two explicits == runtime error)
– Implicit (two implicits == no collation)
– Default
– <no collation>
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
Limitations

Features people will want for future
versions
– LCID --> Collation
– ISO string <--> Collation
– Creating custom collations?
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
Questions?
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)
Unicode and Collation Support
in Microsoft SQL Server
26 April 2001
Unicode and Collation Support in MS
SQL Server, IUC 18 (Hong Kong)