IDENTITY列及其编号的问题

最新推荐文章于 2023-03-13 16:50:23 发布

chen_xizhang

最新推荐文章于 2023-03-13 16:50:23 发布

阅读量360

点赞数

分类专栏： [随笔分类]SQL Server 文章标签： statistics insert 存储 output 数据库 table

本文链接：https://blog.csdn.net/chen_xizhang/article/details/4262376

版权

[随笔分类]SQL Server 专栏收录该内容

108 篇文章 0 订阅

订阅专栏

该篇文章是我于2009年6月10日通过自己编写的工具，批量从位于在博客园的博客站点(http://chenxizhang.cnblogs.com)同步而来。文章中的图片地址仍然是链接到博客园的。特此说明！

陈希章

原文地址：http://www.cnblogs.com/chenxizhang/archive/2008/09/03/1283018.html
原文标题：IDENTITY列及其编号的问题
原文发表：2008/9/3 7:37:00

在数据库设计中，我们为了让某一个列的数值能够自动地增长，会使用标识列（IDENTITY)，标识列使用起来很简单，只要字段数据类型是整数，通过设置一个属性即可完成该操作

这个列是自动递增的，换句话说，它也是只读的。

有一个问题估计我们都已经发现了，就是例如我们开始一个事务，然后向这个表插入一条记录，然后我们撤销了事务。这个时候，我们显然希望那个标识列的编号也能撤销。但事与愿违。我们发现最后在表中的数据，OrderID是不连续的。

这个情况在很多时候没有什么大不了的，尤其是这个OrdeID只是用来唯一标识而已的话。

但是，如果某些时候，我们希望这些编号能够连续，那么该怎么办呢？很显然的是，IDENTITY列本身是做不了这样的事情的。我们得想一些方法才行。

首先，我们创建另外一个额外的表。这个表用来存放编号

CREATE TABLE [dbo].[OrderID](
[ID] [int] NOT NULL,
CONSTRAINT [PK_OrderID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

它看起来像是这样

是的，那没有什么好奇怪的——它只有一个字段。当然，建完这个表之后，你最好是给它一个初始值，例如0

接下来，我们编写一个存储过程

ALTER PROC [dbo].[GetNextOrderID](@ID INT OUTPUT)
AS
BEGIN
    DECLARE @MAXID INT
    SELECT @MAXID=MAX(ID) FROM dbo.OrderID
    SET @ID=@MAXID+1
    INSERT INTO dbo.OrderID VALUES(@MAXID+1)
END

这个存储过程如此简单，以至于我不觉得有必要进行更多的介绍

接下来，我们看看如何在插入订单记录的存储过程中去调用上面这个存储过程

CREATE PROC InsertOrder(
@CustomerID NVARCHAR(50)
)
AS
BEGIN
DECLARE @ID INT
EXEC GetNextOrderID @ID OUTPUT

INSERT INTO [OrderDB].[dbo].[Order]
           ([OrderID]
           ,[CustomerID]
           ,[OrderDate])
     VALUES
           (@ID
           ,@CustomerID
           ,GETDATE())

END

好吧，最后你可以用以下代码测试一下看看效果

BEGIN TRAN
EXEC InsertOrder 'Chenxizhang'
ROLLBACK TRAN

你会发现那个编号并没有因为事务回滚而断开了，而是连续的。这是为什么呢？

作者：陈希章
出处：http://blog.csdn.net/chen_xizhang
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。