自动递增 SQL 值

2022-01-16 00:00:00 sql sql-server stored-procedures sql-server-2008-r2 table-valued-parameters

根据我现在工作的一家公司的全球 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 行违反主键约束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.

相关文章