I think that

SELECT @Output = SCOPE_IDENTITY()
FROM BigFatTable

should just be

SELECT @Output = SCOPE_IDENTITY()

Hmmm... Well, I agree with Wayne that SCOPE_IDENTITY() isn't table specific. You'd use SELECT IDENT_CURRENT(BigFatTable) for that.

But I don't see why this worked in Dev and Test.

SCOPE_IDENTITY() is scoped by the module (stored procedure, trigger, function, batch) it's in. If the goal is to get the last identity value inserted into BigFatTable in this module, I see no reason why this shouldn't work. If the goal is to get the latest identity value inserted into BigFatTable (this module or not), then IDENT_CURRENT() would be what you want.

Given the code:

--
INSERT INTO BigFatTable(Name, Description, Time)
VALUES (@Name, @Description, @Time)

INSERT INTO AnotherTable(This, That, TheOther)
VALUES (@This, That, TheOther)

SELECT @BigFatIdentity = SCOPE_IDENTITY()
--

You'll the identity inserted into AnotherTable in @BigFatIdentity, not the identity in BigFatTable.

Now, assume that the last line uses IDENT_CURRENT(BigFatTable), but the following code runs in a different module between the two inserts:

--
INSERT INTO BigFatTable(Name, Description, Time)
VALUES (@AnotherName, @AnotherDescription, @AnotherTime)
--

In this case, you do get an identity value from BigFatTable into @BigFatIdentity, but it's not the one you wanted because the value from IDENT_CURRENT was bumped by the second batch. Now change the first batch to:

--
INSERT INTO BigFatTable(Name, Description, Time)
VALUES (@Name, @Description, @Time)

SELECT @BigFatIdentity = SCOPE_IDENTITY()

INSERT INTO AnotherTable(This, That, TheOther)
VALUES (@This, @That, @TheOther)
--

Assume that the second batch above (the second insert into BigFatTable) runs after the first insert and before the SCOPE_IDENTITY() select. Now @BigFatIdentity will have the proper value for the identity field inserted into BigFatTable in this batch. The out-of-scope insert doesn't affect SCOPE_IDENTITY(), and because you're getting SCOPE_IDENTITY() before inserting into another table it's not going to get overwritten by the second insert.

Now, my guess at the problem here is that the trailing "FROM BigFatTable" is the culprit. If you look at the execution plan with and without that part, you'll see that having the FROM clause results in a table scan over the entire BigFatTable (which one can assume from the naming convention will be very big, and thus costly to scan), while removing the FROM clause ends up with a very trivial select. So, it fell down in production not because @output was being set wrong, but because it was choking on the huge and unnecessary table scan while trying to set @output.

Excellent analysis, Todd; I could not have said it any better.

The addition of that FROM clause essentially caused @Output to be assigned with the last IDENTITY value from the current scope *iteratively for each row in the table*. It was correct, but comically redundant.

Although I did say that this code was tested, it should be pretty obvious that it was never performance tested under the expected scale. In test, BigFatTable obviously never got large enough during testing for that table scan to hurt them.

If anyone reading this far cannot yet see the problem, try running this in pubs:

-----------------------------------------
DECLARE @host varchar(50)
DECLARE @complete varchar(8000)

SELECT @host = HOST_NAME() FROM authors

SELECT @complete = ' '
SELECT @complete = @complete + ';' + HOST_NAME() FROM authors

SELECT @host, @complete
-----------------------------------------

Now, try to explain the output to yourself.

The utter error is your assumption of your team's perfectness.

Sarchasm: (n) The gap between the author of sarcastic wit and the person who doesn't get it.

Todd hit it perfectly.

Table scans on big fat tables are not fun in production environments!

Use This

IDENT_CURRENT('table_name')

Returns the last identity value generated for a specified table in any session and any scope.


SELECT @Output = IDENT_CURRENT('BigFatTable')



OR



SCOPE_IDENTITY()

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. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

SELECT @Output = SCOPE_IDENTITY()


I wold Choose
the SELECT @Output = IDENT_CURRENT('BigFatTable')
since when using OLDB to call the server a siralizable isolation level is initiated on the stor proc hance no concurrency issues

PingBack from http://chaespot.com/mssql/2006/09/13/eric-bowens-net-technology-blog-sql-server-t-sql-code-samples/

PingBack from http://chaespot.com/mssql/2006/09/24/hans-vbs-weblog-sql-2005-net-vs-t-sql-the-meaningless-3/

PingBack from http://chaespot.com/mssql/2006/10/03/microsoft-sql-server-survival-guide/