2

I'm using an SqlCommand like so:

command.CommandText = "INSERT INTO ... VALUES ...; SELECT SCOPE_IDENTITY();";

Is this enough, or do i need BEGIN TRAN etc.? (Mentioned here.)

I tried it first, of course, and it works fine. But will it work correctly even if there are two simultaneous inserts? (And I'm not sure how to test that.)

Community
  • 1
  • 1
ispiro
  • 26,556
  • 38
  • 136
  • 291

3 Answers3

5

You don't need BEGIN TRAN. Scope_Identity() functions fine without it. Even if there are "simultaneous inserts". That is the whole point of the function--to return an answer for the current scope only.

Be aware that in less than SQL Server 2012, parallelism can break Scope_Identity(), so you must use the query hint WITH (MAXDOP 1) on your INSERT statement if you want it to work properly 100% of the time. You can read about this problem on Microsoft Connect. (It is theoretically fixed in Cumulative Update package 5 for SQL Server 2008 R2 Service Pack 1, but some people seem to think that may not be 100% true).

There is also the OUTPUT clause in SQL Server 2005 and up, which is another way to return data about your INSERT, either by sending a rowset to the client or by outputting to a table. Be aware that receiving the rowset does not actually prove the INSERT was properly committed... so you should probably use SET XACT_ABORT ON; in your stored procedure. here's an example of OUTPUT:

CREATE TABLE @AInsert(IDColumn);

INSERT dbo.TableA (OtherColumn) -- not the identity column
OUTPUT Inserted.IDColumn -- , Inserted.OtherColumn, Inserted.ColumnWithDefault
   INTO @AInsert
SELECT 'abc';

-- Do something with @AInsert, which contains all the `IDColumn` values
-- that were inserted into the table. You can insert all columns, too,
-- as shown in the comments above
Paul
  • 25,812
  • 38
  • 124
  • 247
ErikE
  • 48,881
  • 23
  • 151
  • 196
2

Not exactly the answer to your question, but if you are on SQL Server 2005 and above, consider using the OUTPUT clause, take a look this so answer for full sample, it's simple enough to implement

INSERT dbo.MyTable (col1, col2, col3)
OUTPUT INSERTED.idCol
VALUES ('a', 'b', 'c')
Community
  • 1
  • 1
Jason
  • 3,844
  • 1
  • 21
  • 40
1

Scope_Identity and Begin Tran work independently, begin tran is used when you might want to rollback or commit a transaction at a given point within your query.

Scott
  • 19
  • 2
  • 6
  • In addition, the `INSERT` statement is contained with an implicit transaction; in other words, there is essentially a `BEGIN TRAN` before `INSERT` and a `COMMIT TRAN` between the end of `INSERT...` and `SELECT SCOPE_IDENTITY()` – tommy_o Jun 12 '13 at 22:49