T-SQL開發——ID處理篇

發糞塗牆發表於2012-06-23
SQL

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

問題現象：

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


說明：

在SQLServer中，支援多種自動產生序號的機制。
第一種是根據資料的插入自動生成序號用於識別每個資料行。稱為【Identity】。作用在同一個表層面。
第二種是作用在資料庫層面，叫做timestamp資料型別，稱為rowversion。通過這個，可以讓相同資料庫中不同資料列產生唯一識別碼。
第三種是似乎用NEWID()或NEWSEQUENTIALID()產生Uniqueidentifier的資料型別。這個型別是全球級別的唯一識別碼。號稱3000年內不會重複。
合理使用上面三鍾方式，能減輕應用程式的負擔。

下面對每種情況做詳細講解：

1、資料表級別識別——Identity：

這種識別方式只適合在表級別。使用時只需要在insert語句中搭配即可，不用指定該列的名稱。另外，它會自動增加，比如在DELETE語句中刪除某行，後續的資料仍然會從最近的一行序號中自加。而不會從原始的定義起始開始重新自增。以下給出一個例子：
use tempdb
go
--建立測試用的資料表
create table Employee
(
en int not null identity,	--自增ID
ename varchar(50),			--員工名稱
keyDT datetime				--建立日期
);

--插入資料,不指定列名
insert into Employee
values('Lewis','2012/6/23');

--插入資料，指定列名，但不指定自增列
insert into Employee(ename,keyDT)
values('Ada','2012/6/24')
go
select * 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 tempdb
go
--建立測試用的資料表
CREATE TABLE T1
(
	XID INT NOT NULL IDENTITY,
	XNAME VARCHAR(10)
);
GO
CREATE 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 t1
after insert
as 
	insert into t2(YNAME)
		select xname from inserted
GO

--編寫儲存過程將資料新增到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是4
EXEC uspTest 'Ada'

注意：Identity作為自增時，就算在相同事件裡面都不會產生相同的序號，所以可以但非強制作為表的主索引鍵。

2、資料庫級別標識——timestamp ：

這個功能主要使用資料庫的計數器產生的時間戳，產生每個資料的識別。這種資料的屬性是timestamp，也稱為rowversion。為指定資料庫的任何資料表產生唯一的戳值。戳值就是一種二進位制資料型別，長度等於varbinary(8)。另外，這種型別還會根據後續針對這行資料的修改，改變原有timestamp值。由於它的動態性，在選作索引值時要評估。
該值可以使用@@DBTS系統函式來獲取。
以下是示例程式碼：
use tempdb
go
--建立南方員工的資料表
CREATE TABLE Employee_S
(
	en timestamp not null,--自增二進位制ID
	ename varchar(50),--員工名
	keyDT datetime	--建立時間
)

--建立中部員工的資料表
CREATE TABLE Employee_C
(
	en timestamp not null,--自增二進位制ID
	ename varchar(50),--員工名
	keyDT datetime	--建立時間
)

--建立北方員工的資料表
CREATE TABLE Employee_N
(
	en timestamp not null,--自增二進位制ID
	ename 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_S
union all
select '中部',* from Employee_C
union all
select '北方',* from Employee_N
結果如下：

執行指令碼後看到資料的日期是一樣的，但是en列不一樣，而這種效果是identity做不到的。

3、使用NEWID()搭配UniqueIdentifier資料產生全球唯一標識碼：

該值通過隨機搭配多種配置資訊，產生全球性的唯一識別碼。以下是一個示例程式碼：
use tempdb
go
--建立南方員工的資料表
CREATE TABLE Employee_GUID
(
	en uniqueidentifier not null,--自增二進位制ID
	ename 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',en
from Employee_GUID
group by en
having count(1)>1

另外，在前面提到過，可以使用NEWID()和NEWSEQUENTIALID()產生， 考慮NEWID()和NEWSEQUENTIALID()兩者在使用上的區別：
use tempdb
go
--產生NEWID()和NEWSEQUENTIALID()：
SET NOCOUNT ON 
DECLARE @T TABLE (newSN uniqueidentifier,seqSN uniqueidentifier default (NEWSEQUENTIALID()))
DECLARE @I INT
SET @I=1 
WHILE @I<=10
BEGIN
	INSERT INTO @T VALUES(NEWID(),DEFAULT)
		SET @I=@I+1
END

SELECT * FROM @T
SET 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 tempdb
go
--建立當天序號表
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)=0
		begin
			insert into tabSN values(000000,GETDATE())
		end

	--取出序號表中的日期
	DECLARE @sndt datetime
	set @sndt=(select sndt from tabSN);

	--判斷是否發生跨天情況，，若是則移動到歷史表
	if CONVERT(char(10),@sndt,111)<>CONVERT(char(10),getdate(),111)
	begin
		insert into tabSNHist select * from tabSN;
		truncate table  tabSN;
		insert into tabSN values(000000,getdate())
	end
	--將號碼累加1，作為最後操作時間
	update tabsn set sn=sn+1 ,sndt=GETDATE()
	--出去序號，轉換成YYYYMMDDNNNNNN
	SELECT @sn=CONVERT(VARCHAR(10),SNDT,112)+RIGHT('000000'+CONVERT(VARCHAR(6),SN),6)
	FROM tabSN;
	COMMIT TRANSACTION
	GO


	--使用儲存過程產生序號
	DECLARE @SN CHAR(14)
	EXEC uspSN @SN OUTPUT
	SELECT @SN 'SN'

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

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

以下程式碼在4個視窗中同時執行：
declare @cnt int
set @cnt=1
while @cnt<=100
begin
	--執行儲存過程
	declare @sn char(14)
	exec uspsn @sn output
	--將結果新增到測試資料表
	insert into test
	select @sn,GETDATE(),'SPID'+convert(varchar(5),@@spid)
	set @cnt=@cnt+1
	waitfor delay '00:00:01'
end
go

可以使用以下語句來測試是否有重複：
	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 INT
SET @MIN=(SELECT CONVERT(INT,RIGHT(MIN(SN),6)) FROM TEST)
SET @MAX=(SELECT CONVERT(INT,RIGHT(MAX(SN),6)) FROM TEST)
WHILE @MIN<=@MAX
BEGIN
	INSERT INTO @T VALUES(@MIN)
		SET @MIN=@MIN+1
END
SELECT 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 TEMPDB
GO
--建立訂單表，訂單號是主索引鍵不可以重複
--建立時間使用GETDATE()值
CREATE TABLE FruitOrderList
(
	orderID		varchar(20) not null primary key,
	prodID		int,
	qty			int,
	region		varchar(10),
	keyinDT		datetime default (getdate())
);
GO
--建立INSTEAD OF觸發器
CREATE TRIGGER Tri_Int_FruitOrderList ON FruitOrderList
INSTEAD 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 FruitOrderList
	WHERE CONVERT(char(10),keyinDT,111)=CONVERT(CHAR(10),GETDATE(),111)

	--重新進行資料新增操作
	INSERT INTO FruitOrderList
	SELECT @oSN,prodID,qty,region,keyinDT
	FROM inserted
	SET NOCOUNT OFF
GO

然後可以嘗試做一下批量插入:
--測試操作:
--新增資料,注意訂單編號是自動產生:
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 FruitOrderList
GO

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


注意事項:

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

在前端應用程式輸出時自動加上序號：

問題：在前端應用程式展現資料時，希望能自動加上序號。

解決方法：

可以使用ROW_NUMBER()函式，使用方式：
ROW_NUMBER() OVER([分割子句]<排序子句>)
使用ROW_NUMBER()解決自動產生序號的時候，需要指定哪個資料航排序。
USE AdventureWorks
GO
--使用FirstName進行序號的輸出排序
SELECT ROW_NUMBER() OVER(ORDER BY FirstName),FirstName,JobTitle,EmailAddress
FROM HumanResources.vEmployee
WHERE JobTitle LIKE '%Engineer%'
GO




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

相關文章