Safest way to get the last inserted ID to be unique - SQL

Question

I know that the SCOPE_IDENTITY() will get the last inserted row from insert statement. However, for the following case, I am not too sure is SCOPE_IDENTITY() is safe. As SELECT MAX(ID) FROM TableA will have go through scan the table to get the max id and it will have performance issue, even slightly, I believe.

Here is the case:

DECLARE @DaysInMonth INT
DECLARE @FirstID    INT
DECLARE @SecondID   INT
DECLARE @ThirdID    INT
DECLARE @FourthID   INT

SET @DaysInMonth = DAY(EOMONTH('2016-09-01'))

    BEGIN TRY
        BEGIN TRANSACTION
            WHILE @DaysInMonth > 0
                BEGIN
                    -- First Insert -- Begin
                    INSERT INTO tableA ('first insert - ' + @DaysInMonth)
                    -- First Insert -- End

                    SET @FirstID = SCOPE_IDENTITY()

                    -- Second Insert -- Begin
                    INSERT INTO tableB ('second insert - ' + @DaysInMonth)
                    -- Second Insert -- End

                    SET @SecondID = SCOPE_IDENTITY()

                    -- Third Insert -- Begin
                    INSERT INTO tableC ('third insert - ' + @DaysInMonth)
                    -- Third Insert -- End

                    SET @ThirdID = SCOPE_IDENTITY()

                    -- Fourth Insert -- Begin
                    INSERT INTO tableD ('fourth insert - ' + @DaysInMonth)
                    -- Fourth Insert -- End

                    SET @FourthID = SCOPE_IDENTITY()

                    SET @DaysInMonth = @DaysInMonth - 1
                END
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION

        THROW
    END CATCH

As from the case above, I have to insert the records every loop for fourth times for how many days in the month that I have declared.

From what I know, there are 4 to get the last inserted ID:

SCOPE_IDENTITY
@@IDENTITY
SELECT MAX(ID) FROM tableA
IDENT_CURRENT

From the following post: Post

Is mentioned that SCOPE_IDENTITY() is what generally that you want to use.

What I mean with 'Safe' is, do the ID will be unique during the loop?

Thank you.

Add one more in your list which serves same purpose `IDENT_CURRENT` — Jaydip Jadhav, Sep 30 '16 at 10:50
I think you got your ans since you already gone through the post which you included in Question — Jaydip Jadhav, Sep 30 '16 at 10:53
I'm pretty sure that this procedural approach could be replaced with a set-based solution easily. In this case you'd insert all rows at once and could use the [OUTPUT-clause](https://msdn.microsoft.com/en-us/library/ms177564.aspx) — Shnugo, Sep 30 '16 at 10:53
@JaydipJ: Yes, I am normally used `SCOPE_IDENTITY` to get the last inserted ID, but only for one `INSERT` statement. As from multiple `INSERT` statement, I am not too sure. — Reinhardt, Sep 30 '16 at 10:55
Why would `select max(ID)` have to scan the entire table? That would make the ID pretty pointless. And it *certainly* isn't safe. I'd go with Shnugo's solution - it's the cleanest and safest. — Luaan, Sep 30 '16 at 10:55
@Shnugo: Thank you for the suggestion, will try to look on that and implement on my case. But, is the `OUTPUT` will have to go through scan the table to get the ID? Or it is even better than my question (list) above? — Reinhardt, Sep 30 '16 at 10:57
Well, it's not like the documentation for `SCOPE_IDENTITY` is obscure or anything. It gives you the last identity value that has been inserted in a command executed in the same scope (in this case, this is the entire stored procedure execution context). — Luaan, Sep 30 '16 at 10:57
@Luaan: As because it have to go though 'SELECT' statement right? and it have to loop through to get the last ID from the table. Correct me if I'm wrong. — Reinhardt, Sep 30 '16 at 10:58
Well, if you're not sure, that's what "include execution plan" is for. Execute the query, and see what the execution plan looks like. Unless the ID column has no index (in which case it's a lousy ID column :D), it will do a simple index seek. And I wouldn't be too surprised if `max(ID)` was almost a no-op on an identity column, especially if its also a primary key and the clustered index. After all, if it needed to check every single row in the table to give you `max(ID)`, it would also have to check every single row to see if the row you're inserting is unique, wouldn't it? Ouch? :D — Luaan, Sep 30 '16 at 11:01
@Luaan: okay then, gonna try that :D. And for the last statement of yours, it makes sense. But, what about if the last inserted record is deleted from the table? the `max(ID)` will not be correct (Just thought about it earlier while write this comment) — Reinhardt, Sep 30 '16 at 11:04

score 2 · Answer 1 · answered Sep 30 '16 at 11:04

You can use OUTPUT column in the last insert statement, Ofcourse this is another option where you will get what exactly input statement executed.. Below is just an example

CREATE TABLE #tablea (
  id int IDENTITY (1, 1),
  val char(10)
)

DECLARE @outputtbl TABLE (
  id int,
  val char(10)
)

INSERT INTO #tablea (val)
OUTPUT INSERTED.* INTO @outputtbl
  VALUES ('test')

SELECT id
FROM @outputtbl

Safest way to get the last inserted ID to be unique - SQL

1 Answers1