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 l Uses the "N" or national data types from the SQL-92 specification l NCHAR, NVARCHAR, NTEXT l What the SQL-99 spec says about Unicode l 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 l No Unicode support at all l One code page per server l One collation per server l 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 l Unicode datatypes supported l Two collations – Unicode – Non-Unicode l Number of collations distilled down to the minimum necessary l 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 l 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 l 43 language collations – Added for unique code pages – Added for unique ordering l 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 l 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 l Server l Database l Column l Expression 26 April 2001 Unicode and Collation Support in MS SQL Server, IUC 18 (Hong Kong)

At the server level l Acts as a default for all databases l Can be changed with Rebuild. M. exe in the toolsBINN dir l 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 l Every database has a collation (default is the server collation) l 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 l Overrides database level collation l Specifies code page for non-Unicode columns l Again, can be changed under some circumstances l 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 l Can be used to override any other collation l 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] l 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 l 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)