查询以获取下一个身份? [英] Query to get the next identity?

问题描述

查询以获取下一个身份?这对于没有删除记录的表是可能的:

Query to get the next identity? This is possible for table without deleted records:

SELECT TOP 1 EMPID + 1 FROM Employee ORDER BY EMPID DESC

如果有删除的数据，我将如何获得下一个身份?例如我有一个这样的表:

If there is deleted data, How I will get the next identity ? For example I have a table like this:

EMPID    NAME
4001     someName
4002     someName
4003 ----------------------- this is deleted
4004     someName
4005     someName
4006     someName
4007     someName
4008     someName
4009 ----------------------- this is deleted
4010 ----------------------- this is deleted

输出必须是 4011

The ouput must be 4011

推荐答案

要在应用程序表单上可靠地显示 IDENTITY 值的唯一方法是 INSERT IT FIRST.IDENT_CURRENT 在您是唯一测试它的人时似乎对您有所帮助，但我可以向您保证，一旦有多个用户使用您的应用程序，这将很快崩溃.也很容易证明.创建下表:

The only way for you to reliably show an IDENTITY value on your application's form is to INSERT IT FIRST. IDENT_CURRENT might seem to help you when you're the only person testing it, but I can assure you this will fall apart quite quickly once multiple users are using your application. It is very easy to prove, too. Create the following table:

CREATE TABLE dbo.whatever(ID INT IDENTITY(1,1), blat CHAR(1));

现在，在两个单独的 Management Studio 窗口中，首先运行此代码，它模拟您将在表单上显示的内容，如果您遵循已接受的答案以及您所说的有效":

Now, in two separate Management Studio windows, first run this code, which simulates what you'd be showing on the form, if you follow the accepted answer and what you said "works":

SELECT IDENT_CURRENT('dbo.whatever');

注意输出(两者都应该是 1).这是对的.到目前为止.

Note the output (both should be 1). This is correct. SO FAR.

现在，在一个窗口中运行:

Now, in one window, run this:

INSERT dbo.whatever(blat) SELECT 'x';
SELECT SCOPE_IDENTITY();

输出应该是 1(这也是正确的SO FAR).

The output should be 1 (which, again, is correct SO FAR).

现在，在另一个窗口中，运行相同的操作，但将 x 更改为 y.这个输出现在是 2.呃-哦.这与您在此用户的表单上显示的内容不符.您还可以通过查看表中有两行来验证这一点，1 和 2 作为 IDENTITY 值:

Now, in the other window, run the same thing, but change x to y. This output is now 2. UH-OH. This does not match what you showed this user on their form. You can also validate that by seeing there are two rows in the table, with 1 and 2 as the IDENTITY values:

SELECT ID, blat FROM dbo.whatever;

正确的方法，也是唯一的方法，就是插入一行，检索值，然后将它显示在表单上.如果您需要事先向他们显示一些代理值(不知道为什么需要这样做，或者为什么您的最终用户无论何时检索它都需要知道这个值 - 为什么用户关心 ID 是什么?)，然后创建一个单独的表并在那里生成您的 IDENTITY 值.

The right way to do this, and the only way to do it, is to insert a row, retrieve the value, and then show it on the form. If you need to show them some surrogate value beforehand (no idea why you would need to do this, or why your end users need to know this value no matter when you retrieve it - why do users care what the ID is?), then create a separate table and generate your IDENTITY values there.

CREATE TABLE dbo.dummy_table(ID INT IDENTITY(1,1) PRIMARY KEY);
GO
CREATE TABLE dbo.real_table(ID INT PRIMARY KEY, ...other columns...);
GO

现在，当您想在表单上显示下一个"ID 时，可以使用以下方法:

Now, when you want to show the "next" ID on the form, you can do so using the following:

INSERT dbo.dummy_table DEFAULT VALUES;
SELECT SCOPE_IDENTITY();

然后当用户填写其余信息时，您可以插入到 dbo.real_table 并在插入列表中包含 ID 列，使用您的值从 dbo.dummy_table 检索.请注意，如果用户看到一个 ID 并且没有点击保存，这仍然会导致空白，但该 ID 现在毫无意义，因为它从未进入真正的表格 - 其他任何人都不可能已经看到了(与使用 IDENT_CURRENT、MAX+1 和其他构思不良的先检查值"技术可能发生的情况不同).

Then when the user fills out the rest of the information, you can insert into dbo.real_table and include the ID column in the insert list, using the value you retrieved from dbo.dummy_table. Note that this will still end up with gaps in the event that a user saw an ID and didn't click save, but that ID is now meaningless as it never made it into the real table - and there is no possibility for anyone else to have seen it (unlike what can happen with IDENT_CURRENT, MAX+1 and other ill-conceived "check the value first" techniques).

如果您的实际目标是将这本书的三份副本插入另一个表中，那么解决方案非常简单，仍然需要您先插入这本书.让我们假设您有参数来表示书名和份数(以及我确定的其他参数):

If your actual goal is to insert three copies of the book into another table, the solution is quite simple, and still requires that you insert the book first. Let's assume you have parameters to represent the name of the book and the number of copies (as well as other parameters I'm sure):

DECLARE @Copies INT, @name NVARCHAR(32);

SELECT @Copies = 3, @name = N'Moby Dick';

现在，我们可以插入到 dbo.Books 表中，并使用输出将多行插入到另一个表中 (dbo.Accession?).无需先盲目猜测BookID的下一个"值.

Now, we can insert into the dbo.Books table, and use the output to insert multiple rows into the other table (dbo.Accession?). No need to blindly guess at the "next" value of BookID first.

DECLARE @BookID INT;

INSERT dbo.Books(name, copies, whatever...) SELECT N'Moby Dick', 3, ...;

SELECT @BookID = SCOPE_IDENTITY();

INSERT dbo.Accession(AccessionID, BookID)
  SELECT rn, @BookID
  FROM
  (
    SELECT TOP (@Copies) rn = ROW_NUMBER() OVER (ORDER BY [object_id])
    FROM sys.columns ORDER BY [object_id]
  ) AS y;

这使用一种技巧从目录视图生成多行，但您也可以使用内置的 Numbers 表(如果有)以提高效率(并减少权限限制).

This uses a trick to generate multiple rows from catalog views, but you can also use a built-in Numbers table, if you have one, for improved efficiency (and less restrictive permissions).

这篇关于查询以获取下一个身份?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！