關於SQL SERVER定序

 

一、定序簡介：

什麼叫定序呢？MS是這樣描述的："在 Microsoft SQL Server  中，
字串的實體儲存體由定序控制。定序指定表示每個字元的位元模式以及存
儲和比較字元所使用的規則。"
　　在查詢分析器內執行下面語句，可以得到SQL　SERVER支援的所有定序。
　　　　select * from ::fn_helpcollations()
定序名稱由兩部份構成，前半部份是指本定序所支援的字元集。
如：
　　Chinese_PRC_CS_AI_WS
前半部份：指UNICODE字元集，Chinese_PRC_指標對大陸簡體字UNICODE的定序，按拼音排序。
Chinese_PRC_Stroke 表示按漢字筆畫排序；
定序的後半部份即尾碼 含義：
　　_BIN 二進位排序
　　_CI(CS) 是否區分大小寫，CI不區分，CS區分(case-insensitive/case-sensitive)
　　_AI(AS) 是否區分重音，AI不區分，AS區分(accent-insensitive/accent-sensitive)
　　_KI(KS) 是否區分假名類型,KI不區分，KS區分(kanatype-insensitive/kanatype-sensitive)
　　_WI(WS) 是否區分寬度 WI不區分，WS區分(width-insensitive/width-sensitive)　
區分大小寫:如果想讓比較將大寫字母和小寫字母視為不等，請選擇該選項。
區分重音:如果想讓比較將重音和非重音字母視為不等，請選擇該選項。如果選擇該選項，
比較還將重音不同的字母視為不等。
區分假名:如果想讓比較將片假名和平假名日語音節視為不等，請選擇該選項。
區分寬度:如果想讓比較將半形字元和全形字元視為不等，請選擇該選項。

二、修改、查看定序：

------修改列的定序
ALTER TABLE tb
ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CI_AS

------修改資料庫的定序
ALTER DATABASE database
COLLATE Chinese_PRC_CS_AS

------查看某個表的定序
select collation from syscolumns
where id=object_id(N'yourtablename')

五、定序應用：

　　SQL SERVER提供了大量的WINDOWS和SQLSERVER專用的定序，但它的應用往往
被開發人員所忽略。其實它在實踐中大有用處。

　　例1:讓表NAME列的內容按拼音排序：

create table #t(id int,name varchar(20))
insert #t select 1,'中'
union all select 2,'國'
union all select 3,'人'
union all select 4,'阿'

select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS
drop table #t
/*結果：
id          name                
----------- --------------------
4           阿
2           國
3           人
1           中
*/

　　例2：讓表NAME列的內容按姓氏筆劃排序：

create table #t(id int,name varchar(20))

insert #t select 1,'三'
union all select 2,'乙'
union all select 3,'二'
union all select 4,'一'
union all select 5,'十'
select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS 
drop table #t
/*結果：
id          name                
----------- --------------------
4           一
2           乙
3           二
5           十
1           三
*/

 

 

三、常見問題處理：

 1.“無法解決 equal to 操作的定序衝突。”

樣本1：

create table #t1(
name varchar(20) collate Albanian_CI_AI_WS, 
value int)

create table #t2(
name varchar(20) collate Chinese_PRC_CI_AI_WS,   
value int )

表建好後，執行串連查詢：

select * from #t1 A inner join #t2 B on A.name=B.name

這樣，錯誤就出現了：

           伺服器: 訊息 446，層級 16，狀態 9，行 1
           無法解決 equal to 操作的定序衝突。
　　要排除這個錯誤，最簡單方法是，表串連時指定它的定序，這樣錯誤就
不再出現了。語句這樣寫：

select *
from #t1 A inner join #t2 B
on A.name=B.name collate Chinese_PRC_CI_AI_WS

樣本2：

例如，在建立表時考慮使用下面的 Transact-SQL 陳述式：

CREATE TABLE TestTab (

id int,

GreekCol nvarchar(10) COLLATE greek_ci_as,

LatinCol nvarchar(10) COLLATE latin1_general_cs_as

)

INSERT TestTab VALUES (1, N'A', N'a')

GO

該語句建立了一個包含以下兩列的表：一列使用不區分大小寫和區分重音的希臘語定序，而另一列使用區分大小寫和重音的通用 Latin1 定序。

您可以嘗試使用查詢來顯式比較這兩列：

SELECT *

FROM TestTab

WHERE GreekCol = LatinCol

但是，該查詢會返回一個錯誤：

Msg 446, Level 16, State 9, Server V-MICHKA3, Line 1

無法解決等於運算的定序衝突。

之所以會出現此錯誤，是因為伺服器無法使用不同的定序來比較兩段文本。但是，如果您使用 COLLATE 關鍵字顯式建立一個允許這兩列相容的運算式，則查詢將以如下方式執行：

SELECT *

FROM TestTab

WHERE GreekCol = LatinCol COLLATE greek_ci_as

還需注意的是，儘管 LatinCol 通常有一個區分大小寫定序，但運算式不區分大小寫定序會將其覆蓋，從而使“A”的大寫和小寫被視為等同。