SQL SERVER字符集的研究

一、 試驗歸類
測試SQL:
------------------------------------------------------------
drop table a
create table a(a varchar(2))

insert into a values('a')
insert into a values(N'a')
insert into a values('深圳')
insert into a values(N'深圳')
select a, len(a), datalength(a) from a

drop table #a
create table a(a varchar(2))

insert into #a values('a')
insert into #a values(N'a')
insert into #a values('深圳')
insert into #a values(N'深圳')
select a, len(a), datalength(a) from #a

------------------------------------------------------------
drop table a
create table a(a varchar(8000))

insert into a select REPLICATE('a', 8000)
insert into a select REPLICATE('深', 8000)
insert into a select REPLICATE(N'a', 8000)
insert into a select REPLICATE(N'深', 8000)
select a, len(a), datalength(a) from a

1. 字符集是支援雙位元組的字符集如中文字符集（Collation name為Chinese_PRC_CI_AS）
 
 <1>. 定義varchar(2)
 
  (1) 正式表
   總結：在中文字符集下，定義varchar(x),
    不論使用不使用N''，英文字元都佔1個位元組,即可以存x個英文字元;
      不論使用不使用N''，中文字元都佔2個位元組,即可以存(x / 2)箇中文，select結果為漢字本身，不是亂碼；
      
  (2) 臨時表
   總結：在中文字符集下，定義varchar(x), 
    和正式表表現一樣；
  
 <2>. 定義nvarchar(2)
 
  (1) 正式表
   總結：在中文字符集下，定義nvarchar(x),
    不論使用不使用N''，英文字元都佔2個位元組,即可以存x個英文字元;
      不論使用不使用N''，中文字元都佔2個位元組,即可以存x箇中文，select結果為漢字本身，不是亂碼；
      
  (2) 臨時表
   總結：在中文字符集下，定義nvarchar(x), 
    和正式表表現一樣；
    
 <3>. 型別為varchar時，長度 x 和 datalength()對應，都指位元組大小；
    英文len() = datalength();
    中文len() = datalength() / 2;
    
    型別為nvarchar時，長度 x 和 len()對應，都指字元長度； 
   
2. 字符集是支援單位元組的字符集如拉丁字符集（Collation name為Latin1_General_CI_AS）
  
 <1>. 定義varchar(2)
 
  (1) 正式表
   總結：在英文字符集下，定義varchar(x),
    不論使用不使用N''，英文字元都佔1個位元組,即可以存x個英文字元;
      不論使用不使用N''，中文字元都佔1個位元組,即可以存x箇中文，但只儲存前半截中文編碼，所以select結果為亂碼；
      （特殊：如果使用N''，此時插入的字元數最大為4000）
      英文和中文 len() = datalength()；
      
  (2) 臨時表
   總結：在英文字符集下，定義varchar(x),
    不論使用不使用N''，英文字元都佔1個位元組,即可以存x個英文字元;
    不使用N''時，中文佔1個位元組，可以存x個漢字，但都只存入漢字前半截字元編碼，顯示為亂碼；
    使用N''時，中文佔2個位元組，只可以存 x/2 個漢字，沒有亂碼，取出仍為漢字,說明在英文字符集下透過使用N''是可以儲存漢字的；
    
    除用N''儲存的中文外，其餘英文和中文 len() = datalength()；
    用N''儲存的中文字元len() = datalength() / 2；
    
 <2>. 定義nvarchar(2)
 
  (1) 正式表
   總結：在英文字符集下，定義nvarchar(x),
    不論使用不使用N''，英文字元都佔2個位元組,即可以存x個英文字元;（注意每個字元比varchar用的空間大）
      不論使用不使用N''，中文字元都佔2個位元組,即可以存x箇中文字元，
      但不使用N''只儲存前半截中文編碼，所以select結果為亂碼；
      使用N''則儲存和取出都為漢字本身；
      
  (2) 臨時表
   總結：在英文字符集下，定義nvarchar(x), 
    和正式表表現相同；
    
 <3>. 型別為varchar時，長度 x 和 datalength()對應，都指位元組大小；
    （臨時表中N''中文字元長度比較特殊；）
    型別為nvarchar時，長度 x 和 len()對應，都指字元長度； 
 
二、 使用歸類
 拋開不常用的臨時表不談，只看正式表，再加上varchar和nvarchar型別的最大長度，得到以下經驗：
 <1> 最大長度問題
  1. 在中文字符集下使用varchar，最大長度可定義8000,這個8000是指位元組數（datalength()），即最大可以儲存8000個英文字元，4000箇中文字元；
   特殊：若存入字元N'a'，則最大能儲存4000個字元，但其所佔空間為4000位元組；
  2. 在中文字符集下使用nvarchar，最大長度可定義4000,這個4000是指字元個數（len()），即最大可以儲存4000個英文字元，4000箇中文字元；
 
  3. 在英文字符集下使用varchar，最大長度可定義8000,這個8000是指位元組數（datalength()），由於中文英文都儲存為1位元組，故最大可以儲存8000個英文、中文字元；
  4. 在英文字符集下使用nvarchar，最大長度可定義4000,這個4000是指字元個數（len()），即最大可以儲存4000個英文字元，4000箇中文字元；
 <2> 文字顯示問題
  1. N''要和資料型別nvarchar, nchar一起使用，如果對varchar, char欄位型別強制使用N''，則會產生一些特殊現象，甚至無法控制； 
  2. 在英文字符集下，想要儲存特殊符號字元、中文等雙位元組字元，在定義表結構時要使用nvarchar或者nchar，在儲存時要用N''；
  3. 在中文字符集下，資料庫系統預設已經可以儲存特殊符號字元、中文等雙位元組字元。即使用不使用N''，都按雙位元組處理。但為了統一期間建議：
   在定義表結構時如果使用nvarchar或者nchar，在儲存時要用N''，
   在定義表結構時如果使用varchar和char，此時不要使用N''操作；
  4. SUBSTRING ( expression , start , length )
   length：是一個整數，指定子串的長度（要返回的字元數或位元組數）。
   中文字符集中按字元數取；
   英文字符集中，char, varchar按位元組數取，nchar, nvarchar按字元數取；

 
三、 其他參考
 使用 Unicode 資料
  unicode內碼表、排序規則、SQL Server 排序規則基礎知識、Windows 排序規則排序樣式、選擇 SQL 排序規則
 DBCS 字元

[@more@]