T-SQL開發——ID處理篇

標籤：

原文: T-SQL開發——ID處理篇

資料庫自增ID功能中Identity、Timestamp、Uniqueidentifier的區別：問題現象：

一般序號的產生，對於一般程式員而言，都是使用T-SQL命令來實現。先讀取表中的最大需要，然後累加一，再插回資料庫，這樣做是相當危險的。因為如果事務機制沒有處理好，就會出現同時間內取得同一序號。結果可想而知。為了避免這種情況，SQLServer在內部已經提供了一定的機制來協助處理。

說明：在SQLServer中，支援多種自動產生序號的機制。第一種是根據資料的插入自動產生序號用於識別每個資料行。稱為【Identity】。作用在同一個表層面。第二種是作用在資料庫層面，叫做timestamp資料類型，稱為rowversion。通過這個，可以讓相同資料庫中不同資料列產生唯一識別碼。第三種是似乎用NEWID()或NEWSEQUENTIALID()產生Uniqueidentifier的資料類型。這個類型是全球層級的唯一識別碼。號稱3000年內不會重複。合理使用上面三鐘方式，能減輕應用程式的負擔。
下面對每種情況做詳細講解：1、資料表層級識別——Identity：這種識別方式只適合在表層級。使用時只需要在insert語句中搭配即可，不用指定該列的名稱。另外，它會自動增加，比如在DELETE語句中刪除某行，後續的資料仍然會從最近的一行序號中自加。而不會從原始的定義起始開始重新自增。以下給出一個例子：

use tempdbgo--建立測試用的資料表create table Employee(en int not null identity,--自增IDename varchar(50),--員工名稱keyDT datetime--建立日期);--插入資料,不指定列名insert into Employeevalues(‘Lewis‘,‘2012/6/23‘);--插入資料，指定列名，但不指定自增列insert into Employee(ename,keyDT)values(‘Ada‘,‘2012/6/24‘)goselect * from Employee

結果如下：

針對Identity，還有一些提示：1、Identity(n,m)：n為自增起始值，m為自增數量，可以實現(n,n+m,n+2m,n+3m..)這樣的資料。2、@@identity系統函數：該用處在執行階段，用於捕獲最近一次插入資料所產生的自增號。在應用程式中非常遊泳，比如新增一個新資料，然後擷取該id，接著用於查詢顯示。3、IDENT_CURRENT(‘資料表名‘)：可以找出指定表的目前最大自增號，可以取代SELECT MAX語句，加快查詢。特別是在大並發的時候，如果用SELECT MAX可能會出現擷取不正確的序號，而且當表非常大的時候SELECT MAX也是需要很長時間的。4、SCOPE_IDENTITY()函數：在預存程序、觸發器執行過程中的自增加號數。但和@@identity有些不同，@@identity返回的是整個事務中的目前值，而本函數僅返回該預存程序、觸發器程式中的新增資料表的號碼。@@identity在一個事務有延伸或調用另外一個表的INDENTITY屬性是，會產生差異，而本函數主要用於處理這種問題。下面展示@@identity和SCOPE_IDENTITY()的差異：

use tempdbgo--建立測試用的資料表CREATE TABLE T1(XID INT NOT NULL IDENTITY,XNAME VARCHAR(10));GOCREATE TABLE T2(YID INT NOT NULL IDENTITY,YNAME VARCHAR(10));GO--插入3條資料到T2表中INSERT INTO T2(YNAME) VALUES(‘name1‘),(‘name2‘),(‘name3‘);GO--建立T1的INSERT觸發器，用於將T1的資料自動新增到T2的資料表中CREATE TRIGGER tri_t1 ON t1after insertas insert into t2(YNAME)select xname from insertedGO--編寫預存程序將資料新增到t1資料表自動返回scope_identity()和@@Identity的值create  PROC uspTest(@name varchar(10))as insert into t1 values(@name)select @@IDENTITY ‘@@identity‘,SCOPE_IDENTITY() ‘scope_identity‘,‘In Proc‘as  ‘scope‘go--使用預存程序測試：當scope_identity()是1時，@@identity是4EXEC uspTest ‘Ada‘

注意：Identity作為自增時，就算在相同事件裡面都不會產生相同的序號，所以可以但非強製作為表的主索引鍵。
2、資料庫層級標識——timestamp ：這個功能主要使用資料庫的計數器產生的時間戳記，產生每個資料的識別。這種資料的屬性是timestamp，也稱為rowversion。為指定資料庫的任何資料表產生唯一的戳值。戳值就是一種位元據類型，長度等於varbinary(8)。另外，這種類型還會根據後續針對這行資料的修改，改變原有timestamp值。由於它的動態性，在選作索引值時要評估。該值可以使用@@DBTS系統函數來擷取。以下是範例程式碼：

use tempdbgo--建立南方員工的資料表CREATE TABLE Employee_S(en timestamp not null,--自增二進位IDename varchar(50),--員工名keyDT datetime--建立時間)--建立中部員工的資料表CREATE TABLE Employee_C(en timestamp not null,--自增二進位IDename varchar(50),--員工名keyDT datetime--建立時間)--建立北方員工的資料表CREATE TABLE Employee_N(en timestamp not null,--自增二進位IDename varchar(50),--員工名keyDT datetime--建立時間)--插入資料：insert into Employee_S(ename,keyDT) values(‘Sname‘,GETDATE())insert into Employee_C(ename,keyDT) values(‘Cname‘,GETDATE())insert into Employee_N(ename,keyDT) values(‘Nname‘,GETDATE())--顯示資料select ‘南方‘,* from Employee_Sunion allselect ‘中部‘,* from Employee_Cunion allselect ‘北方‘,* from Employee_N

結果如下：
執行指令碼後看到資料的日期是一樣的，但是en列不一樣，而這種效果是identity做不到的。
3、使用NEWID()搭配UniqueIdentifier資料產生全球唯一標識碼：該值通過隨機搭配多種配置資訊，產生全球性的唯一識別碼。以下是一個範例程式碼：

use tempdbgo--建立南方員工的資料表CREATE TABLE Employee_GUID(en uniqueidentifier not null,--自增二進位IDename varchar(50)--員工名)--插入資料：insert into Employee_GUID(en,ename) values(newid(),‘Sname‘),(newid(),‘Cname‘),(newid(),‘Nname‘)--顯示資料,為了證明不唯一，可以使用GROUP BY來檢驗：--來源資料select *from Employee_GUID--檢驗資料select count(1) ‘Total‘,enfrom Employee_GUIDgroup by enhaving count(1)>1

另外，在前面提到過，可以使用NEWID()和NEWSEQUENTIALID()產生， 考慮NEWID()和NEWSEQUENTIALID()兩者在使用上的區別：

use tempdbgo--產生NEWID()和NEWSEQUENTIALID()：SET NOCOUNT ON DECLARE @T TABLE (newSN uniqueidentifier,seqSN uniqueidentifier default (NEWSEQUENTIALID()))DECLARE @I INTSET @I=1 WHILE @I<=10BEGININSERT INTO @T VALUES(NEWID(),DEFAULT)SET @[email protected]+1ENDSELECT * FROM @TSET NOCOUNT OFF 

執行後可以看到：注意每台機器值會不一樣

從圖上可以看出，NEWSEQUENTIALID()會產生一個有次序的GUID值（觀察值的第一部分），這樣可以在做比較時起作用。而NEWID()則為沒有次序的值。注意事項：1、使用Identity作為行的標識時，無法結合事務的使用保留下一個使用的號碼。即當事務發生Rollback時，依然會出去一個號碼，而不會釋放，會造成跳號現象。2、使用Truncate可以重設IDENTITY最後識別的值。而DELETE計算全部刪除資料，下一行資料依舊會從原有的上一筆開始，不會重新開始。3、使用Timestamp類型時，僅適合那些不會UPDATE操作的資料。因為會更新timestamp值。
通過預存程序實現定製化產生序號方式：問題現象：在很多情況下，由於使用需要，往往不能僅靠上面提到的3中方式產生序號。而要組合成一些有意義的號碼。但是這種情況就難以保證資料在插入資料庫的時候不重複。
說明：這種情況在多人調用程式時就容易出現。可以從前端應用程式著手，也可以從資料庫開發一些功能來統一產生序號。無論哪種方式，都要做到以下3點才算解決了問題：1、給號的過程中，據對不能發生重複。2、給號速度越短越好。3、有些應用程式要求，全部給出去的序號。不能有跳號的情況。在這種情況下，建議混合使用前後端程式來保證，當使用預存程序年時，建議採用OUTPUT參數進行序號的釋放。避免使用資料集的方式回傳，因為使用OUTPUT參數輸出，可以減少資源使用，加快啟動並執行速度。另外搭配資料庫的SET XACT_ABORT ON 選項，及BEGIN TRANSACTION /COMMIT TRANSACTION運算式，保證每次產生的序號過程不會發生事務過程中的Lost Updae。下面是一些範例程式碼：

use tempdbgo--建立當天序號表create table tabSN(sn int,sndt datetime)go--建立曆史序號表create table tabSNHist(sn INT,sndt datetime)go--create proc uspSN (@sn char(14) output)as --開始事務set xact_abort on begin transaction --判斷序號表是否有資料，若沒有則新增一條資料if (select count(1) from tabSN)=0begininsert into tabSN values(000000,GETDATE())end--取出序號表中的日期DECLARE @sndt datetimeset @sndt=(select sndt from tabSN);--判斷是否發生跨天情況，，若是則移動到曆史表if CONVERT(char(10),@sndt,111)<>CONVERT(char(10),getdate(),111)begininsert into tabSNHist select * from tabSN;truncate table  tabSN;insert into tabSN values(000000,getdate())end--將號碼累加1，作為最後操作時間update tabsn set sn=sn+1 ,sndt=GETDATE()--出去序號，轉換成YYYYMMDDNNNNNNSELECT @sn=CONVERT(VARCHAR(10),SNDT,112)+RIGHT(‘000000‘+CONVERT(VARCHAR(6),SN),6)FROM tabSN;COMMIT TRANSACTIONGO--使用預存程序產生序號DECLARE @SN CHAR(14)EXEC uspSN @SN OUTPUTSELECT @SN ‘SN‘

可以做一個簡單的壓力測試來驗證這種寫法是否會產生重複：

--壓力測試--建立表存放測試結果create table test (sn char(14),sdt datetime ,scomm varchar(100)--誰執行了預存程序)

以下代碼在4個視窗中同時執行：

declare @cnt intset @cnt=1while @cnt<=100begin--執行預存程序declare @sn char(14)exec uspsn @sn output--將結果新增到測試資料表insert into testselect @sn,GETDATE(),‘SPID‘+convert(varchar(5),@@spid)set @[email protected]+1waitfor delay ‘00:00:01‘endgo

可以使用以下語句來測試是否有重複：

select count(1), sn from test group by sn having count(1)>1

當然，結果是沒有重複的。也可以檢查是否有跳號情況：

--檢查是否發生跳號：SET NOCOUNT ON DECLARE @T TABLE (TID INT)DECLARE @MAX INT ,@MIN INTSET @MIN=(SELECT CONVERT(INT,RIGHT(MIN(SN),6)) FROM TEST)SET @MAX=(SELECT CONVERT(INT,RIGHT(MAX(SN),6)) FROM TEST)WHILE @MIN<[email protected]BEGININSERT INTO @T VALUES(@MIN)SET @[email protected]+1ENDSELECT TID ‘不連續號碼‘ FROM @T EXCEPT SELECT CONVERT(INT,RIGHT(SN,6)) FROM TEST SET NOCOUNT OFF

通過檢查是沒有跳號的。而最終的結果：

select * from test order by sn

沒有重複和跳號的資料。
通過INSTEAD OF 觸發器，實現定製化序號：問題現象：在需要同時支援大批量資料插入時，也具備有產生獨立專用序號等功能。
說明：如果要同時具備有自動產生序號或類似預存程序中定製複雜序號的功能，可以使用新增情況下的INSTEAD OF觸發器，因為它能取代新增動作，由自己的特殊定義來改變INSERT的操作方式。但是如果INSTEAD OF之後沒有出現INSERT /UPDATE/DELETE這樣的語句，則觸發器就會無效。

解決方案：以下代碼使用INSTEAD OF觸發器，實現批量新增，並根據每一天的訂單總數，從000001開始編號。格式為YYYYMMDD.NNNNNN。

USE TEMPDBGO--建立訂單表，訂單號是主索引鍵不可以重複--建立時間使用GETDATE()值CREATE TABLE FruitOrderList(orderIDvarchar(20) not null primary key,prodIDint,qtyint,regionvarchar(10),keyinDTdatetime default (getdate()));GO--建立INSTEAD OF觸發器CREATE TRIGGER Tri_Int_FruitOrderList ON FruitOrderListINSTEAD OF INSERT AS SET NOCOUNT ON declare @oSN varchar(20) --產生新序號規則=日期+（總筆數+1）SELECT @oSN=CONVERT(VARCHAR(10),GETDATE(),112)+‘.‘+RIGHT(‘000000‘+CONVERT(VARCHAR(6),COUNT(1)+1),6)FROM FruitOrderListWHERE CONVERT(char(10),keyinDT,111)=CONVERT(CHAR(10),GETDATE(),111)--重新進行資料新增操作INSERT INTO FruitOrderListSELECT @oSN,prodID,qty,region,keyinDTFROM insertedSET NOCOUNT OFFGO

然後可以嘗試做一下批量插入:

--測試操作:--新增資料,注意訂單編號是自動產生:INSERT INTO FruitOrderList VALUES(NULL,3,30,‘A‘,GETDATE())INSERT INTO FruitOrderList VALUES(NULL,6,10,‘B‘,GETDATE())INSERT INTO FruitOrderList VALUES(NULL,9,20,‘C‘,GETDATE())INSERT INTO FruitOrderList VALUES(NULL,12,40,‘D‘,GETDATE())SELECT * FROM FruitOrderListGO

從結果可以看到:確實達到了想要的效果.

注意事項:1、INSTEAD OF 觸發器執行時機,會在條件約束Primary key之前.2、執行過程，可以用INSERTED記錄新增的資料後者修改後的資料，使得DELTE記錄刪除的資料或修改前的資料。3、在定義過程中避免使用Cursor，可以直接使用INSERTED或者DELETED來擷取資料。實現迴圈效果。

在前端應用程式輸出時自動加上序號：問題：在前端應用程式展現資料時，希望能自動加上序號。解決方案：可以使用ROW_NUMBER()函數，使用方式：ROW_NUMBER() OVER([分割子句]<排序子句>)使用ROW_NUMBER()解決自動產生序號的時候，需要指定哪個資料航排序。

USE AdventureWorksGO--使用FirstName進行序號的輸出排序SELECT ROW_NUMBER() OVER(ORDER BY FirstName),FirstName,JobTitle,EmailAddressFROM HumanResources.vEmployeeWHERE JobTitle LIKE ‘%Engineer%‘GO

注意事項:ROW_NUMBER()函數的ORDER BY 和SELECT 的ORDER BY 不一致時會影響輸入結果

T-SQL開發——ID處理篇