自动增加 SQL 值 [英] Auto Increment SQL Value

问题描述

在我现在工作的一家公司的全球 DBA 的无限智慧中，他创建了一个表，该表将一个 int 作为 ID 字段，但不会自动递增数字.

In the infinte wisdom of the global DBA at a firm I am working at right now he has created a table that takes an int as an ID field, but does not auto increment the number.

我正在从 .Net 传递一个表值参数，因为它有大约 100 行或更多行的数据在任何时候传递，我不想终止应用程序、破坏网络或 SQL Server.

I am passing up a table valued parameter from .Net because it has roughly 100 or more rows of data that are being passed up at any one time and I dont want to kill the application, hammer the network or the SQL Server.

这是我的存储过程

CREATE PROCEDURE sp_Insert_Supporting_Error_Info

(@tvp [dbo].udt_CEQZW READONLY)

as
begin

INSERT INTO CEQZW 
ERROR_VAR_ID, 
ERROR_ID, 
ERROR_VAR_TYPE_CD, 
ERROR_VAR_VALUE) 
SELECT (SELECT coalesce(MAX(ERROR_VAR_ID), 0) + row_number() over (order by 
(select NULL)) FROM CEQZW) as ERROR_VAR_ID, 
ERROR_ID, 
ERROR_VAR_TYPE_CD, 
ERROR_VAR_VALUE FROM @TVP
end
go

我希望这个 SELECT coalesce(MAX(ERROR_VAR_ID), 0) + row_number() over (order by (select NULL)) FROM CEQZW我用这个测试

I was hoping that this SELECT coalesce(MAX(ERROR_VAR_ID), 0) + row_number() over (order by (select NULL)) FROM CEQZW would some how do the trick for me as when I test with this

declare @p3 dbo.udt_CEQZW
insert into @p3 values(1,N'es',N'test')
insert into @p3 values(1,N'ec',N'test')
insert into @p3 values(1,N'ec',N'test')
insert into @p3 values(1,N'ses',N'test')
insert into @p3 values(1,N'es',N'test')

exec sp_Insert_Supporting_Error_Info @p3

这就是我要回来的

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)消息 2627，级别 14，状态 1，过程 sp_Insert_Supporting_Error_Info，第 9 行违反 PRIMARY KEY 约束PK_CEQZW".无法在对象dbo.CEQZW"中插入重复键.重复的键值为 (1).声明已终止.

(1 row(s) affected) Msg 2627, Level 14, State 1, Procedure sp_Insert_Supporting_Error_Info, Line 9 Violation of PRIMARY KEY constraint 'PK_CEQZW'. Cannot insert duplicate key in object 'dbo.CEQZW'. The duplicate key value is (1). The statement has been terminated.

所以我的问题是，除了锤击网络、应用程序和 SQL Server 自动增量并将 ID 添加到表中之外，我该如何做

So the question I have is how would I, other than hammering the network, app and SQL Server auto increment and add the ID into the table

推荐答案

嗯，我会先去找你的 DBA，问他为什么决定不做 ID 列和标识.也许他会改变主意.

Well, I would start by going to your DBA and ask why he decided to not make the ID column and identity. Perhaps he will change his mind.

但是，如果他会保留这个决定，请不要尝试自己创建自动递增机制.
99.9% 的情况下它有可能失败，尤其是在多用户环境中.
相反，使用标识列的已经内置的线程安全方法.

If, however, he will keep this decision, do not attempt to create an auto-increment mechanism on your own.
99.9% of the cases it has a potential to fail, especially in a multi user environment.
Instead, use the already built in, thread safe method of an identity column.

由于我们讨论的是无法直接在目标表中使用标识列的情况，因此我建议使用 2012 版本中引入的序列对象的简单模拟来获得自动增量.

Since we are talking about a situation where you can't use an identity column directly in your target table, I would suggest using a simple mimic of the sequence object introduced in 2012 version to get you your auto increment.

为此，您需要一个计数(数字)表.如果您的 DBA 尚未创建，请让他阅读 Jeff Moden 的 The Numbers"或Tally"表:它是什么以及它如何替换循环，然后将他发送给 KM. 在 this SO post 用于创建脚本.(方法 7 是我最喜欢的.)

For this, you'll need a tally (numbers) table. If your DBA did not already create one, send him to to read Jeff Moden's The "Numbers" or "Tally" Table: What it is and how it replaces a loop and then send him to KM.'s answer on this SO post for the creation script. (Method 7 is my favorite.)

现在你有一个数字表，你添加一个非常简单的表:

Now that you have a numbers table, you add a very simple table:

CREATE TABLE tblSequence
(
    Value int identity(1,1)
)

然后，您创建一个存储过程，该过程将向该表中插入任意数量的行并返回新创建的值(感谢 Martin Smith 在 这篇文章！):

Then, you create a stored procedure that will insert any number of rows into this table and returns the newly created values (Thanks to Martin Smith for the merge trick on this post!):

CREATE PROCEDURE stp_GetNextValues
(
    @NumberOfValues as int
)
AS

    MERGE INTO Sequence
    USING (SELECT Number
           FROM   Tally
           WHERE  Number <= @NumberOfValues) T
    ON 1 = 0
    WHEN NOT MATCHED THEN
      INSERT
      DEFAULT VALUES
    OUTPUT INSERTED.Value; 

GO

然后每当您执行此存储过程时，您都会获得安全的自动递增值.

Then whenever you execute this stored procedure you'll get safe auto incremented values.

EXEC stp_GetNextValues 125

您可以在 rextester 上查看完整的脚本.

我让你把它合并到你自己的程序中.

I leave it up to you to incorporate this into your own procedure.

这篇关于自动增加 SQL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！