Unicode practice

Unicode practice

SELECT *
FROM ::fn_helpcollations()

note:now the database collation is:SQL_Latin1_General_CP1_CI_AS

                           USE Northwind
                           GO

--First use the SQL_Latin1_General_CP1_CI_AS for the Nvarchar column,
                           SQL_Latin1_General_CP1_CI_AS:
                           Dictionary order,
                           case-insensitive,
                           for use with 1252 character set

                           CREATE TABLE [UnicodeT1] (
                           [UID] [int] IDENTITY (1, 1) NOT NULL ,
                           [UString] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL ,
                           PRIMARY KEY  CLUSTERED
                           (
                            [UID]
                           )  ON [PRIMARY]
                           )

--We will test the unicode through insert data not spcify the pre-fix N.\
--notice:now use the the 1252 character to store the data.
 

                           INSERT UnicodeT1
                           SELECT 'レ'----Japance character.because we havn't spcify the

N,so use the default 1252 character set to store data. but the 1252 doesn't include the

Chinese character code.there will be store wrong data

                           INSERT UnicodeT1
                           SELECT '中'---Chinese character. because we havn't spcify the

N,so use the default 1252 character set to store data. but the 1252 doesn't include the

Chinese character code.there will be store wrong data

                           INSERT UnicodeT1
                           SELECT 'D'--English character because we havn't spcify the N,so

use the default 1252 character set to store data. but the 1252  include the 'D' character

code.there be right

                           SELECT
                           DATALENGTH(UString) 'Size(Byte)'
                           ,UString
                           ,UNICODE(SUBSTRING(UString,1,1)) 'UString Code'
                           ,NCHAR(UNICODE(SUBSTRING(UString,1,1))) 'UString Char'
                           FROM UnicodeT1
The Result are:
Size(Byte)  UString  [UString code]  [UString char]
2    ?   63      ?
2    ?   63      ?
2    D   68      D

                           INSERT UnicodeT1
                           SELECT N'レ'----Japance Character.Use prefix N to identify the

following characters are unicode

                           INSERT UnicodeT1
                           SELECT N'中'---Chinese Character.Use prefix N to identify the

following characters are unicode

                           INSERT UnicodeT1
                           SELECT N'D'--English Character.Use prefix N to identify the

following characters are unicode

                           SELECT
                           DATALENGTH(UString) 'Size(Byte)'
                           ,UString
                           ,UNICODE(SUBSTRING(UString,1,1)) 'UString Code'
                           ,NCHAR(UNICODE(SUBSTRING(UString,1,1))) 'UString Char'
                           FROM UnicodeT1
                           ORDER BY UID ASC
The Result are:
Size(Byte)  UString  [UString code]  [UString char]
2    ?   63      ?
2    ?   63      ?
2    D   68      D
2    レ   12524     レ
2    中   20013     中
2    D   68      D
                        

                           --
--SECONDLY,we can store Chinese in varchar and char.but we stongly recommand you store data

in nvarchar or nchar.because it is depand on the chinese_prc_ci_AS code page.if we need

store Japanese in the furture.there will be some problem occur.

--the follw demo show how to store Chinese Character in varchar.but we strongly recommand

you don''t store data like this.
                              create table UnicodeT2
                              (
                              CID INT IDENTITY(1,1) PRIMARY KEY,
                              UString varchar(12) collate   CHINESE_PRC_CI_AS
                              )

                              INSERT UnicodeT2
                              SELECT N'中a'
                              INSERT UnicodeT2
                              SELECT N'®'

                              SELECT DATALENGTH(UString) 'Size(Byte)',UString  'UString CHAR' FROM  UnicodeT2
THE RESULT ARE:
Size(Byte)   UString CHAR
3            中a
1            ?
IT CAN STORE THE CHINESE.BUT WE STRONGLY SUGGEST NOT USE THE METHOD TO STORE DATA.