Query to get the next identity? [closed]

问题:

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

The ouput must be 4011

回答1:

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));

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');

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();

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

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;

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

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();

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';

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;

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).

回答2:

Have a look at SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

Please do note the differences, as stqated below.

I would recomend you look at using IDENT_CURRENT.

IDENT_CURRENT (Transact-SQL)

Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

SCOPE_IDENTITY (Transact-SQL)

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

@@IDENTITY (Transact-SQL)

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.