MSSQL Unicode Support

Views:
 
     
 

Presentation Description

No description available.

Comments

By: amrik (130 month(s) ago)

great. apne comments khud hi....

By: puneetsharma20 (130 month(s) ago)

Thanks To : Michael S. Kaplan Software Design Engineer Trigeminal Software, Inc.

By: puneetsharma20 (130 month(s) ago)

CREATE TABLE [CONTENTDATA] ( [RECORDID] nvarchar(64) NOT NULL, [XML] ntext NULL, CONSTRAINT [PK_CONTENTDATA] PRIMARY KEY([RECORDID]) ) insert into CONTENTDATA (RECORDID, XML) values (newid(), N'附属原爆後障害医療研究施設'); In case the japanese characters didn’t come thru the mail list: insert into CONTENTDATA (RECORDID, XML) values (newid(), N'some string'); Notice the N in front of the string – that tells SQL Server to store it as unicode for the nchar, ntext, and nvarchar columns (not sure why it wouldn’t do that automatically for those datatypes). It appears that if I do an INSERT using the above notation the only way I can retrieve the data is appending the N into the qualifier as well. For example: select * from CONTENTDATA where XML like N'附属原%'; -- will retrieve the record inserted above select * from CONTENTDATA where XML like '附属原%'; -- will NOT retrieve the record inserted above

Presentation Transcript

Unicode and Collation Support in Microsoft SQL Server : 

26 April 2001 Unicode and Collation Support in MS SQL Server, IUC 18 (Hong Kong) Unicode and Collation Support in Microsoft SQL Server Michael S. Kaplan Software Design Engineer Trigeminal Software, Inc.

Unicode Support : 

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

Collation in SQL Server <= 6.5 : 

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

Collation in SQL Server 7.0 : 

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

Collation in SQL Server 2000 : 

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

"Windows" collations : 

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)

SQL Collations : 

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

Collation at four levels : 

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

At the server level : 

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

At the database level : 

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

At the column level : 

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

At the expression level : 

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

More on 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>

Limitations : 

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?

Questions? : 

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

Slide 16: 

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

authorStream Live Help