Unicode and Collation Support in Microsoft SQL Server

Document Sample
Unicode and Collation Support in Microsoft SQL Server Powered By Docstoc
					Unicode and Collation Support
   in Microsoft SQL Server



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




                    Unicode and Collation Support in MS
26 April 2001
                     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




                  Unicode and Collation Support in MS
26 April 2001
                   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




                Unicode and Collation Support in MS
26 April 2001
                 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

                  Unicode and Collation Support in MS
26 April 2001
                   SQL Server, IUC 18 (Hong Kong)
  Collation in SQL Server 2000
   Combined code pages and collations into a
    single entity




                Unicode and Collation Support in MS
26 April 2001
                 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)


                         Unicode and Collation Support in MS
26 April 2001
                          SQL Server, IUC 18 (Hong Kong)
                SQL Collations
   Provided for backwards compatibility with
    prior versions of SQL Server




                 Unicode and Collation Support in MS
26 April 2001
                  SQL Server, IUC 18 (Hong Kong)
                Collation at four levels
 Server
 Database
 Column
 Expression




                      Unicode and Collation Support in MS
26 April 2001
                       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'))




                   Unicode and Collation Support in MS
26 April 2001
                    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




                     Unicode and Collation Support in MS
26 April 2001
                      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
                    Unicode and Collation Support in MS
26 April 2001
                     SQL Server, IUC 18 (Hong Kong)
            At the expression level
 Can be used to override any other collation
 uses the COLLATE keyword




                 Unicode and Collation Support in MS
26 April 2001
                  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>


                    Unicode and Collation Support in MS
26 April 2001
                     SQL Server, IUC 18 (Hong Kong)
                   Limitations
   Features people will want for future
    versions
      – LCID --> Collation
      – ISO string <--> Collation
      – Creating custom collations?




                   Unicode and Collation Support in MS
26 April 2001
                    SQL Server, IUC 18 (Hong Kong)
                Questions?




                Unicode and Collation Support in MS
26 April 2001
                 SQL Server, IUC 18 (Hong Kong)
    Unicode and Collation Support
      in Microsoft SQL Server




                Unicode and Collation Support in MS
26 April 2001
                 SQL Server, IUC 18 (Hong Kong)