數(shù)據(jù)庫自增ID功能中Identity、Timestamp、Uniqueidentifier的區(qū)別：

問題現(xiàn)象：

一般序號的產(chǎn)生，對于一般程序員而言，都是使用T-SQL命令來實現(xiàn)。先讀取表中的最大需要，然后累加一，再插回數(shù)據(jù)庫，這樣做是相當(dāng)危險的。因為如果事務(wù)機制沒有處理好，就會出現(xiàn)同時間內(nèi)取得同一序號。結(jié)果可想而知。為了避免這種情況，SQLServer在內(nèi)部已經(jīng)提供了一定的機制來協(xié)助處理。

說明：

在SQLServer中，支持多種自動產(chǎn)生序號的機制。

第一種是根據(jù)數(shù)據(jù)的插入自動生成序號用于識別每個數(shù)據(jù)行。稱為【Identity】。作用在同一個表層面。

第二種是作用在數(shù)據(jù)庫層面，叫做timestamp數(shù)據(jù)類型，稱為rowversion。通過這個，可以讓相同數(shù)據(jù)庫中不同數(shù)據(jù)列產(chǎn)生唯一識別碼。

第三種是似乎用NEWID()或NEWSEQUENTIALID()產(chǎn)生Uniqueidentifier的數(shù)據(jù)類型。這個類型是全球級別的唯一識別碼。號稱3000年內(nèi)不會重復(fù)。

合理使用上面三鐘方式，能減輕應(yīng)用程序的負擔(dān)。

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

1、數(shù)據(jù)表級別識別——Identity：

這種識別方式只適合在表級別。使用時只需要在insert語句中搭配即可，不用指定該列的名稱。另外，它會自動增加，比如在DELETE語句中刪除某行，后續(xù)的數(shù)據(jù)仍然會從最近的一行序號中自加。而不會從原始的定義起始開始重新自增。以下給出一個例子：

      use tempdb

go

--創(chuàng)建測試用的數(shù)據(jù)表

create table Employee

(

en int not null identity,	--自增ID

ename varchar(50),			--員工名稱

keyDT datetime				--創(chuàng)建日期

);



--插入數(shù)據(jù),不指定列名

insert into Employee

values('Lewis','2012/6/23');



--插入數(shù)據(jù)，指定列名，但不指定自增列

insert into Employee(ename,keyDT)

values('Ada','2012/6/24')

go

select * from Employee

結(jié)果如下：

針對Identity，還有一些使用技巧：

1、Identity(n,m)：n為自增起始值，m為自增數(shù)量，可以實現(xiàn)(n,n+m,n+2m,n+3m..)這樣的數(shù)據(jù)。

2、@@identity系統(tǒng)函數(shù)：該用處在執(zhí)行階段，用于捕獲最近一次插入數(shù)據(jù)所產(chǎn)生的自增號。在應(yīng)用程序中非常游泳，比如新增一個新數(shù)據(jù)，然后獲取該id，接著用于查詢顯示。

3、IDENT_CURRENT('數(shù)據(jù)表名')：可以找出指定表的目前最大自增號，可以取代SELECT MAX語句，加快查詢。特別是在大并發(fā)的時候，如果用SELECT MAX可能會出現(xiàn)獲取不正確的序號，而且當(dāng)表非常大的時候SELECT MAX也是需要很長時間的。

4、SCOPE_IDENTITY()函數(shù)：在存儲過程、觸發(fā)器執(zhí)行過程中的自增加號數(shù)。但和@@identity有些不同，@@identity返回的是整個事務(wù)中的目前值，而本函數(shù)僅返回該存儲過程、觸發(fā)器程序中的新增數(shù)據(jù)表的號碼。@@identity在一個事務(wù)有延伸或調(diào)用另外一個表的INDENTITY屬性是，會產(chǎn)生差異，而本函數(shù)主要用于處理這種問題。

下面展示@@identity和SCOPE_IDENTITY()的差異：

      use tempdb

go

--創(chuàng)建測試用的數(shù)據(jù)表

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條數(shù)據(jù)到T2表中

INSERT INTO T2(YNAME) VALUES('name1'),('name2'),('name3');

GO



--建立T1的INSERT觸發(fā)器，用于將T1的數(shù)據(jù)自動新增到T2的數(shù)據(jù)表中

CREATE TRIGGER tri_t1 ON t1

after insert

as 

	insert into t2(YNAME)

		select xname from inserted

GO



--編寫存儲過程將數(shù)據(jù)新增到t1數(shù)據(jù)表自動返回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



--使用存儲過程測試：當(dāng)scope_identity()是1時，@@identity是4

EXEC uspTest 'Ada'

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

2、數(shù)據(jù)庫級別標(biāo)識——timestamp ：

這個功能主要使用數(shù)據(jù)庫的計數(shù)器產(chǎn)生的時間戳，產(chǎn)生每個數(shù)據(jù)的識別。這種數(shù)據(jù)的屬性是timestamp，也稱為rowversion。為指定數(shù)據(jù)庫的任何數(shù)據(jù)表產(chǎn)生唯一的戳值。戳值就是一種二進制數(shù)據(jù)類型，長度等于varbinary(8)。另外，這種類型還會根據(jù)后續(xù)針對這行數(shù)據(jù)的修改，改變原有timestamp值。由于它的動態(tài)性，在選作索引值時要評估。

該值可以使用@@DBTS系統(tǒng)函數(shù)來獲取。

以下是示例代碼：

      use tempdb

go

--創(chuàng)建南方員工的數(shù)據(jù)表

CREATE TABLE Employee_S

(

	en timestamp not null,--自增二進制ID

	ename varchar(50),--員工名

	keyDT datetime	--創(chuàng)建時間

)



--創(chuàng)建中部員工的數(shù)據(jù)表

CREATE TABLE Employee_C

(

	en timestamp not null,--自增二進制ID

	ename varchar(50),--員工名

	keyDT datetime	--創(chuàng)建時間

)



--創(chuàng)建北方員工的數(shù)據(jù)表

CREATE TABLE Employee_N

(

	en timestamp not null,--自增二進制ID

	ename varchar(50),--員工名

	keyDT datetime	--創(chuàng)建時間

)



--插入數(shù)據(jù)：

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())

--顯示數(shù)據(jù)

select '南方',* from Employee_S

union all

select '中部',* from Employee_C

union all

select '北方',* from Employee_N

結(jié)果如下：

執(zhí)行腳本后看到數(shù)據(jù)的日期是一樣的，但是en列不一樣，而這種效果是identity做不到的。

3、使用NEWID()搭配UniqueIdentifier數(shù)據(jù)產(chǎn)生全球唯一標(biāo)識碼：

該值通過隨機搭配多種配置信息，產(chǎn)生全球性的唯一識別碼。以下是一個示例代碼：

      use tempdb

go

--創(chuàng)建南方員工的數(shù)據(jù)表

CREATE TABLE Employee_GUID

(

	en uniqueidentifier not null,--自增二進制ID

	ename varchar(50)--員工名



)



--插入數(shù)據(jù)：

insert into Employee_GUID(en,ename) values(newid(),'Sname'),(newid(),'Cname'),(newid(),'Nname')



--顯示數(shù)據(jù),為了證明不唯一，可以使用GROUP BY來檢驗：

--源數(shù)據(jù)

select *

from Employee_GUID

--檢驗數(shù)據(jù)

select count(1) 'Total',en

from Employee_GUID

group by en

having count(1)>1

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

      use tempdb

go

--產(chǎn)生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

執(zhí)行后可以看到下圖：注意每臺機器值會不一樣

從圖上可以看出，NEWSEQUENTIALID()會產(chǎn)生一個有次序的GUID值（觀察值的第一部分），這樣可以在做比較時起作用。而NEWID()則為沒有次序的值。

注意事項：

1、使用Identity作為行的標(biāo)識時，無法結(jié)合事務(wù)的使用保留下一個使用的號碼。即當(dāng)事務(wù)發(fā)生Rollback時，依然會出去一個號碼，而不會釋放，會造成跳號現(xiàn)象。

2、使用Truncate可以重置IDENTITY最后識別的值。而DELETE計算全部刪除數(shù)據(jù)，下一行數(shù)據(jù)依舊會從原有的上一筆開始，不會重新開始。

3、使用Timestamp類型時，僅適合那些不會UPDATE操作的數(shù)據(jù)。因為會更新timestamp值。

通過存儲過程實現(xiàn)定制化產(chǎn)生序號方式：

問題現(xiàn)象：

在很多情況下，由于使用需要，往往不能僅靠上面提到的3中方式產(chǎn)生序號。而要組合成一些有意義的號碼。但是這種情況就難以保證數(shù)據(jù)在插入數(shù)據(jù)庫的時候不重復(fù)。

說明：

這種情況在多人調(diào)用程序時就容易出現(xiàn)。可以從前端應(yīng)用程序著手，也可以從數(shù)據(jù)庫開發(fā)一些功能來統(tǒng)一產(chǎn)生序號。無論哪種方式，都要做到以下3點才算解決了問題：

1、給號的過程中，據(jù)對不能發(fā)生重復(fù)。

2、給號速度越短越好。

3、有些應(yīng)用程序要求，全部給出去的序號。不能有跳號的情況。

在這種情況下，建議混合使用前后端程序來保證，當(dāng)使用存儲過程年時，建議采用OUTPUT參數(shù)進行序號的釋放。避免使用數(shù)據(jù)集的方式回傳，因為使用OUTPUT參數(shù)輸出，可以減少資源使用，加快運行的速度。

另外搭配數(shù)據(jù)庫的SET XACT_ABORT ON 選項，及BEGIN TRANSACTION /COMMIT TRANSACTION表達式，保證每次產(chǎn)生的序號過程不會發(fā)生事務(wù)過程中的Lost Updae。下面是一些示例代碼：

      use tempdb

go

--創(chuàng)建當(dāng)天序號表

create table tabSN(sn int,sndt datetime)

go

--創(chuàng)建歷史序號表

create table tabSNHist(sn INT,sndt datetime)

go



--

create proc uspSN 

(

	@sn char(14) output

)

as 

	--開始事務(wù)

	set xact_abort on 

	begin transaction 



	--判斷序號表是否有數(shù)據(jù)，若沒有則新增一條數(shù)據(jù)

	if (select count(1) from tabSN)=0

		begin

			insert into tabSN values(000000,GETDATE())

		end



	--取出序號表中的日期

	DECLARE @sndt datetime

	set @sndt=(select sndt from tabSN);



	--判斷是否發(fā)生跨天情況，，若是則移動到歷史表

	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()

	--出去序號，轉(zhuǎn)換成YYYYMMDDNNNNNN

	SELECT @sn=CONVERT(VARCHAR(10),SNDT,112)+RIGHT('000000'+CONVERT(VARCHAR(6),SN),6)

	FROM tabSN;

	COMMIT TRANSACTION

	GO





	--使用存儲過程產(chǎn)生序號

	DECLARE @SN CHAR(14)

	EXEC uspSN @SN OUTPUT

	SELECT @SN 'SN'

可以做一個簡單的壓力測試來驗證這種寫法是否會產(chǎn)生重復(fù)：

      --壓力測試



--創(chuàng)建表存放測試結(jié)果

create table test 

(

	sn char(14),

	sdt datetime ,

	scomm varchar(100)--誰執(zhí)行了存儲過程

)

以下代碼在4個窗口中同時執(zhí)行：

      declare @cnt int

set @cnt=1

while @cnt<=100

begin

	--執(zhí)行存儲過程

	declare @sn char(14)

	exec uspsn @sn output

	--將結(jié)果新增到測試數(shù)據(jù)表

	insert into test

	select @sn,GETDATE(),'SPID'+convert(varchar(5),@@spid)

	set @cnt=@cnt+1

	waitfor delay '00:00:01'

end

go

可以使用以下語句來測試是否有重復(fù)：

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

當(dāng)然，結(jié)果是沒有重復(fù)的。

也可以檢查是否有跳號情況：

        --檢查是否發(fā)生跳號：

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 '不連續(xù)號碼' FROM @T EXCEPT SELECT CONVERT(INT,RIGHT(SN,6)) FROM TEST 

SET NOCOUNT OFF

通過檢查是沒有跳號的。

而最終的結(jié)果：

        select * from test order by sn

沒有重復(fù)和跳號的數(shù)據(jù)。

通過INSTEAD OF 觸發(fā)器，實現(xiàn)定制化序號：

問題現(xiàn)象：

在需要同時支援大批量數(shù)據(jù)插入時，也具備有產(chǎn)生獨立專用序號等功能。

說明：

如果要同時具備有自動產(chǎn)生序號或類似存儲過程中定制復(fù)雜序號的功能，可以使用新增情況下的INSTEAD OF觸發(fā)器，因為它能取代新增動作，由自己的特殊定義來改變INSERT的操作方式。

但是如果INSTEAD OF之后沒有出現(xiàn)INSERT /UPDATE/DELETE這樣的語句，則觸發(fā)器就會無效。

解決方法：

以下代碼使用INSTEAD OF觸發(fā)器，實現(xiàn)批量新增，并根據(jù)每一天的訂單總數(shù)，從000001開始編號。格式為YYYYMMDD.NNNNNN。

      USE TEMPDB

GO

--創(chuàng)建訂單表，訂單號是主索引鍵不可以重復(fù)

--創(chuàng)建時間使用GETDATE()值

CREATE TABLE FruitOrderList

(

	orderID		varchar(20) not null primary key,

	prodID		int,

	qty			int,

	region		varchar(10),

	keyinDT		datetime default (getdate())

);

GO

--創(chuàng)建INSTEAD OF觸發(fā)器

CREATE TRIGGER Tri_Int_FruitOrderList ON FruitOrderList

INSTEAD OF INSERT 

AS SET NOCOUNT ON 

	declare @oSN varchar(20) --產(chǎn)生新序號規(guī)則=日期+（總筆數(shù)+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)



	--重新進行數(shù)據(jù)新增操作

	INSERT INTO FruitOrderList

	SELECT @oSN,prodID,qty,region,keyinDT

	FROM inserted

	SET NOCOUNT OFF

GO

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

      --測試操作:

--新增數(shù)據(jù),注意訂單編號是自動產(chǎn)生:

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

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

注意事項:

1、INSTEAD OF 觸發(fā)器執(zhí)行時機,會在條件約束Primary key之前.

2、執(zhí)行過程，可以用INSERTED記錄新增的數(shù)據(jù)后者修改后的數(shù)據(jù)，使得DELTE記錄刪除的數(shù)據(jù)或修改前的數(shù)據(jù)。

3、在定義過程中避免使用Cursor，可以直接使用INSERTED或者DELETED來獲取數(shù)據(jù)。實現(xiàn)循環(huán)效果。

在前端應(yīng)用程序輸出時自動加上序號：

問題：在前端應(yīng)用程序展現(xiàn)數(shù)據(jù)時，希望能自動加上序號。

解決方法：

可以使用ROW_NUMBER()函數(shù)，使用方式：

ROW_NUMBER() OVER([分割子句]<排序子句>)

使用ROW_NUMBER()解決自動產(chǎn)生序號的時候，需要指定哪個數(shù)據(jù)航排序。

      USE AdventureWorks

GO

--使用FirstName進行序號的輸出排序

SELECT ROW_NUMBER() OVER(ORDER BY FirstName),FirstName,JobTitle,EmailAddress

FROM HumanResources.vEmployee

WHERE JobTitle LIKE '%Engineer%'

GO

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

T-SQL開發(fā)——ID處理篇

更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯(lián)系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

日韩久久久精品,亚洲精品久久久久久久久久久,亚洲欧美一区二区三区国产精品 ,一区二区福利

數(shù)據(jù)庫自增ID功能中Identity、Timestamp、Uniqueidentifier的區(qū)別：

問題現(xiàn)象：

說明：

1、數(shù)據(jù)表級別識別——Identity：

2、數(shù)據(jù)庫級別標(biāo)識——timestamp ：

3、使用NEWID()搭配UniqueIdentifier數(shù)據(jù)產(chǎn)生全球唯一標(biāo)識碼：

注意事項：

通過存儲過程實現(xiàn)定制化產(chǎn)生序號方式：

問題現(xiàn)象：

說明：

通過INSTEAD OF 觸發(fā)器，實現(xiàn)定制化序號：

問題現(xiàn)象：

說明：

解決方法：

注意事項:

在前端應(yīng)用程序輸出時自動加上序號：

問題：在前端應(yīng)用程序展現(xiàn)數(shù)據(jù)時，希望能自動加上序號。

解決方法：

數(shù)據(jù)庫自增ID功能中Identity、Timestamp、Uniqueidentifier的區(qū)別：

2、數(shù)據(jù)庫級別標(biāo)識——timestamp ：

3、使用NEWID()搭配UniqueIdentifier數(shù)據(jù)產(chǎn)生全球唯一標(biāo)識碼：

通過INSTEAD OF 觸發(fā)器，實現(xiàn)定制化序號：

問題：在前端應(yīng)用程序展現(xiàn)數(shù)據(jù)時，希望能自動加上序號。