问题描述
根据我现在工作的一家公司的全球 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.
这篇关于自动递增 SQL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！