原文:
T-SQL開發——ID處理篇
針對Identity，還有一些使用技巧：
注意：Identity作為自增時，就算在相同事件里面都不會產生相同的序號，所以可以但非強制作為表的主索引鍵。
執行腳本后看到數據的日期是一樣的，但是en列不一樣，而這種效果是identity做不到的。
另外，在前面提到過，可以使用NEWID()和NEWSEQUENTIALID()產生， 考慮NEWID()和NEWSEQUENTIALID()兩者在使用上的區別：
可以做一個簡單的壓力測試來驗證這種寫法是否會產生重復：
以下代碼在4個窗口中同時執行：
可以使用以下語句來測試是否有重復：
當然，結果是沒有重復的。
通過檢查是沒有跳號的。
然后可以嘗試做一下批量插入:
從結果可以看到:確實達到了想要的效果.
數據庫自增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 不一致時會影響輸入結果
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
