IDENTITY列及其编号的问题

在数据库设计中，我们为了让某一个列的数值能够自动地增长，会使用标识列（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

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