SQL Server：插入下一个可用的 int
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4853361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: insert next available int
提问by Paul Wieland
I'm dealing with a table in SQL Server that has a serial_nocolumn, which is defined as a non null int. It doesn't appear to be an auto incrementing field, as if I leave that column out of my insert statement I get an error saying that the serial_nofield cannot be null.
我正在处理 SQL Server 中的一个表，该表有一serial_no列定义为非空 int。它似乎不是一个自动递增字段，就好像我将该列从插入语句中删除一样，我收到一条错误消息，指出该serial_no字段不能为空。
So how do I insert the next available number?
那么如何插入下一个可用号码呢？
I tried this:
我试过这个：
INSERT INTO mytable (serial_no) VALUES ( (SELECT MAX(serial_no)+1 FROM mytable))
but I get an error saying that subqueries cannot be used in this context.
但是我收到一条错误消息，说不能在这种情况下使用子查询。
EDIT: This table is used in an off the shelf product so I cannot change the design and make the serial_no column an auto increment.
编辑：此表用于现成产品，因此我无法更改设计并使 serial_no 列成为自动增量。
回答by gbn
You can improve write concurrency with locking hints
您可以使用锁定提示提高写入并发性
INSERT INTO mytable (serial_no, value)
SELECT MAX (serial_no)+1, @value
FROM mytable WITH (ROWLOCK, XLOCK, HOLDLOCK)
If performance is't important, try TABLOCKX in place of ROWLOCK, XLOCK
如果性能不重要，请尝试使用 TABLOCKX 代替 ROWLOCK、XLOCK
However, given this isn't safe either you need to retry
但是，鉴于这不安全，您需要重试
DECLARE @retry bit
SET @retry = 1
WHILE @Retry = 1
BEGIN
BEGIN TRY
INSERT INTO mytable (serial_no, value)
SELECT MAX (serial_no)+1, @value
FROM mytable WITH (ROWLOCK, XLOCK, HOLDLOCK)
SET @Retry = 0
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2627 --PK violation
RAISERROR ('blah', 16, 1)
END CATCH
END
Or change to an IDENTITY column and do it properly...
或者更改为 IDENTITY 列并正确执行...
回答by Conrad Frix
The error can be fixed by dropping the VALUES
可以通过删除 VALUES 来修复错误
INSERT INTO mytable (serial_no, value)
SELECT MAX(serial_no)+1 ,
@value
FROM mytable)
But this is a bad idea. There's a race condition on MAX(serial_no)+1 (e.g. two Inserts get the same value for Max(Serial_no).
但这是一个坏主意。MAX(serial_no)+1 存在竞争条件（例如，两个插入获得相同的 Max(Serial_no) 值。
You're better off using an auto increment field. You could also create a table that stores the current next value and increment it instead of using max.
最好使用自动增量字段。您还可以创建一个表来存储当前的下一个值并增加它而不是使用最大值。
回答by Ilya Kogan
INSERT INTO mytable (serial_no) SELECT MAX(serial_no)+1 FROM mytable
回答by Radu Caprescu
Try it without VALUES:
尝试没有 VALUES：
INSERT INTO mytable (serial_no) SELECT MAX(serial_no)+1 FROM mytable
