精準比較文字與UNIQUEIDENTIFIER資料型態

jeffluo

首先先介紹一個GUID在資料庫的產生方式，當應用程式系統無法產生一個唯一值時候，可以有以下幾種方式

1.  IDENTITY

2.  ROWVERSION

3.  SEQUENCE

4.  UNIQUEIDENTIFIER

其中UNIQUEIDENTIFIER的資料屬性，可以使用DB的NEWID()函數，產生一個類似這樣的資料值。

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx ，例如實際為(88B2A026-6049-46F0-AED0-8E940EDF3B01)

其中x值為0-9或是A-F。

基本上使用它時機，就是產生一個亂數值，並且希望是可以全域唯一(Global Unique)。所以首先讓我們驗證一個資料表，同時產生大量包含UNIQUEIDENTIFIER的資料列，並且同時間使用兩連線大量新增，驗證重複性與唯一性。

--第一條連線，建立TABLE與連續新增10000資料 use [TSQL2014] GO IF OBJECT_ID('tblGUID') is not null    DROP TABLE tblGUID GO CREATE TABLE tblGUID (xid INT IDENTITY , --自動加號 xguid uniqueidentifier, --GUID 編碼 xapp sysname default(app_name())--記錄那種應用程式新增 ) GO --連續產生10000 INSERT INTO tblGUID(xguid) SELECT NEWID() GO 10000

--第二條連線，使用SQLCMD新增5000資料 C:\>sqlcmd 1> use TSQL2014 2> INSERT INTO tblGUID(xguid) SELECT NEWID() 3> go 5000

接下來就是開始比較是否會重複，過程中可以開啟【包含實際執行計畫】，查看比較的結果與成本，因為UNIQUEIDENTIFIER欄位屬性，難使用INDEX 去加速查詢，原因就是該類型資料是屬於沒有次序的資料屬性，建立INDEX之後，很快就因為INSERT大量資料之後，讓INDEX發生FRAGMENTATION。

當然結果是不會重複

緊接著重頭戲要來，該部分就是查詢其中一筆資料，根據xguid值找尋資料表中是否對應資料行。

--查詢其中一個GUID是否存在於稍早新增的資料 SELECT * FROM tblGUID WHERE xguid='0296716C-A9B2-4968-917F-51CDC37715BD' GO

如果這時候如果給定指定的xguid欄位的數值過長，依然可以比對出來結果。 L 原因很簡單，因為GUID與文字字串的比較，當輸入文字字串過長的時候，系統會自動TRUNCATE(截斷)過長的字串，所以超過的36個字元之後文字都會被自動截斷。

--查詢其中一個GUID是否存在於稍早新增的資料 SELECT * FROM tblGUID WHERE xguid='0296716C-A9B2-4968-917F-51CDC37715BD.....' GO

要解決上述的字串過長，自動被截斷的狀況，可以參考以下的方式使用CAST AS VARCHAR(MAX)轉換後進行比對。

--使用CAST AS VARCHAR(MAX)轉換後進行比對 --解決字串過長問題轉換成 varchar(max) SELECT * FROM tblGUID WHERE cast(xguid as varchar(max))=cast('0296716C-A9B2-4968-917F-51CDC37715BD' as varchar(max)) GO --過長字串就已經可以被濾掉 SELECT * FROM tblGUID WHERE cast(xguid as varchar(max))=cast('0296716C-A9B2-4968-917F-51CDC37715BD....' as varchar(max)) GO

結果再進一步測試的時候，發現上述的CAST AS VARCHAR(MAX)轉換後進行比對，竟然無法處理多於空白的字串。

--使用CAST AS VARCHAR(MAX)轉換後進行比對 --無法處理空白字元 SELECT * FROM tblGUID WHERE cast(xguid as varchar(max))=cast('0296716C-A9B2-4968-917F-51CDC37715BD' as varchar(max)) GO SELECT * FROM tblGUID WHERE cast(xguid as varchar(max))=cast('0296716C-A9B2-4968-917F-51CDC37715BD   ' as varchar(max)) GO --結果空白無法被處理

為了要解決這樣的狀況，過程中讓我想到就是再轉換成varbinary(max)的方式，因為binary的部分針對空白會視為不一樣，果真就可以順利解決該問題。

--使用CAST AS VARCHAR(MAX)尚未有能力處理空白字元 SELECT * FROM   tblGUID WHERE  cast(xguid as varchar(max))=cast('0296716C-A9B2-4968-917F-51CDC37715BD   ' as varchar(max)) GO --使用CAST AS VARCHAR(MAX)與CAST AS VARBINARY(MAX)轉換後進行比對 SELECT * FROM tblGUID WHERE cast(cast(xguid as varchar(max))as varbinary(max))=       cast(cast('0296716C-A9B2-4968-917F-51CDC37715BD   ' as varchar(max)) as varbinary(max)) GO --結果空白可以被處理

結論

基本上使用資料庫的UNIQUEIDENTIFIER資料型別處理跟字串比較時候，要特別留意，基本上該資料型態長度為CHAR(36)超過的部分，自動會在比較的時候被去除，導致異狀發生。過程中可以搭配CAST AS VARCHAR(MAX)方式，處理過長字元的比較，但是該方式仍無法處理字串緊接空白字元的特殊狀況，唯一可以解決UNIQUEIDENTIFIER資料型別處理跟字串比較時候可能情境，就是搭配CAST AS VARCHAR(MAX)與CAST AS VARBINARY(MAX)兩種組合。當然各位擔心的成本問題，一併分享給大家，就是差異不大，況且任何UNIQUEIDENTIFIER資料型別的欄位，都強烈不建議建立INDEX，因為任何新增UNIQUEIDENTIFIER資料後，該INDEX就會有很高的FRAGMENTATION，導致最佳化執行器捨棄對該索引的使用，另外UNIQUEIDENTIFIER資料欄位，若是有INDEX也會導致新增過程效能變慢。

--成本cost比較 --直接比較，無法處理過長問題 SELECT * FROM  tblGUID WHERE xguid='0296716C-A9B2-4968-917F-51CDC37715BD   ' GO --可以無法處理過長問題，但是無法處理空白字元 SELECT * FROM  tblGUID WHERE cast(xguid as varchar(max))=cast('0296716C-A9B2-4968-917F-51CDC37715BD   ' as varchar(max)) GO --可以處理過長問題，也可以處理空白字元 SELECT * FROM tblGUID WHERE cast(cast(xguid as varchar(max))as varbinary(max))=       cast(cast('0296716C-A9B2-4968-917F-51CDC37715BD   ' as varchar(max)) as varbinary(max)) GO