Unicode and Collation Support in Microsoft SQL Server

1
Unicode and Collation Support in Microsoft SQL
Server

• Michael S. Kaplan
• Software Design Engineer
• Trigeminal Software, Inc.

2
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

3
Collation in SQL Server lt 6.5

• No Unicode support at all
• One code page per server
• One collation per server
• No good solution for multilingual support

4
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

5
Collation in SQL Server 2000

• Combined code pages and collations into a single
  entity

6
"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)

7
SQL Collations

• Provided for backwards compatibility with prior
  versions of SQL Server

8
Collation at four levels

• Server
• Database
• Column
• Expression

9
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'))

10
At the database level

• Every database has a collation (default is the
  server collation)
• Collation can be changed under some circumstances

11
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

12
At the expression level

• Can be used to override any other collation
• uses the COLLATE keyword

13
More on the COLLATE keyword

• COLLATE ltWindows_Collation_namegtltSQL_Collation_N
  ame
• Specific rules of precedence
• Explicit (two explicits runtime error)
• Implicit (two implicits no collation)
• Default
• ltno collationgt

14
Limitations

• Features people will want for future versions
• LCID --gt Collation
• ISO string lt--gt Collation
• Creating custom collations?

15
Questions?
16

• Unicode and Collation Support
• in Microsoft SQL Server