T-SQL开发——ID处理篇

最新推荐文章于 2021-06-15 10:20:54 发布

snskip

最新推荐文章于 2021-06-15 10:20:54 发布

阅读量602

点赞数

分类专栏： SQL SERVER 文章标签：数据库自动编号 ID

SQL SERVER 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库自增ID功能中Identity、Timestamp、Uniqueidentifier的区别：

问题现象：

一般序号的产生，对于一般程序员而言，都是使用T-SQL命令来实现。先读取表中的最大需要，然后累加一，再插回数据库，这样做是相当危险的。因为如果事务机制没有处理好，就会出现同时间内取得同一序号。结果可想而知。为了避免这种情况，SQLServer在内部已经提供了一定的机制来协助处理。

说明：

在SQLServer中，支持多种自动产生序号的机制。

第一种是根据数据的插入自动生成序号用于识别每个数据行。称为【Identity】。作用在同一个表层面。

第二种是作用在数据库层面，叫做timestamp数据类型，称为rowversion。通过这个，可以让相同数据库中不同数据列产生唯一识别码。

第三种是似乎用NEWID()或NEWSEQUENTIALID()产生Uniqueidentifier的数据类型。这个类型是全球级别的唯一识别码。号称3000年内不会重复。

合理使用上面三钟方式，能减轻应用程序的负担。

下面对每种情况做详细讲解：

1、数据表级别识别——Identity：

这种识别方式只适合在表级别。使用时只需要在insert语句中搭配即可，不用指定该列的名称。另外，它会自动增加，比如在DELETE语句中删除某行，后续的数据仍然会从最近的一行序号中自加。而不会从原始的定义起始开始重新自增。以下给出一个例子：

[sql] view plain copy print ?

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

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()的差异：

[sql] view plain copy print ?

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'

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系统函数来获取。

以下是示例代码：

[sql] view plain copy print ?

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

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数据产生全球唯一标识码：

该值通过随机搭配多种配置信息，产生全球性的唯一识别码。以下是一个示例代码：

[sql] view plain copy print ?

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

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()两者在使用上的区别：

[sql] view plain copy print ?

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

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。下面是一些示例代码：

[sql] view plain copy print ?

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'

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'

可以做一个简单的压力测试来验证这种写法是否会产生重复：

[sql] view plain copy print ?

--压力测试
--创建表存放测试结果
create table test
(
sn char(14),
sdt datetime ,
scomm varchar(100)--谁执行了存储过程
)

--压力测试

--创建表存放测试结果
create table test 
(
	sn char(14),
	sdt datetime ,
	scomm varchar(100)--谁执行了存储过程
)

以下代码在4个窗口中同时执行：

[sql] view plain copy print ?

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

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

可以使用以下语句来测试是否有重复：

[sql] view plain copy print ?

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

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

当然，结果是没有重复的。

也可以检查是否有跳号情况：

[sql] view plain copy print ?

--检查是否发生跳号：
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

--检查是否发生跳号：
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

通过检查是没有跳号的。

而最终的结果：

[sql] view plain copy print ?

select * from test order by sn

select * from test order by sn

没有重复和跳号的数据。

通过INSTEAD OF 触发器，实现定制化序号：

问题现象：

在需要同时支援大批量数据插入时，也具备有产生独立专用序号等功能。

说明：

如果要同时具备有自动产生序号或类似存储过程中定制复杂序号的功能，可以使用新增情况下的INSTEAD OF触发器，因为它能取代新增动作，由自己的特殊定义来改变INSERT的操作方式。

但是如果INSTEAD OF之后没有出现INSERT /UPDATE/DELETE这样的语句，则触发器就会无效。

解决方法：

以下代码使用INSTEAD OF触发器，实现批量新增，并根据每一天的订单总数，从000001开始编号。格式为YYYYMMDD.NNNNNN。

[sql] view plain copy print ?

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

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

然后可以尝试做一下批量插入:

[sql] view plain copy print ?

--测试操作:
--新增数据,注意订单编号是自动产生:
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

--测试操作:
--新增数据,注意订单编号是自动产生:
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()解决自动产生序号的时候，需要指定哪个数据航排序。

[sql] view plain copy print ?

USE AdventureWorks
GO
--使用FirstName进行序号的输出排序
SELECT ROW_NUMBER() OVER(ORDER BY FirstName),FirstName,JobTitle,EmailAddress
FROM HumanResources.vEmployee
WHERE JobTitle LIKE '%Engineer%'
GO

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 不一致时会影响输入结果

snskip

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
T-SQL开发——ID处理篇

数据库自增ID功能中Identity、Timestamp、Uniqueidentifier的区别：问题现象：一般序号的产生，对于一般程序员而言，都是使用T-SQL命令来实现。先读取表中的最大需要，然后累加一，再插回数据库，这样做是相当危险的。因为如果事务机制没有处理好，就会出现同时间内取得同一序号。结果可想而知。为了避免这种情况，SQLServer在内部已经提供了一定的机制来协助处理。
复制链接

扫一扫