|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2019/7/29 13:27 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]How to get the value of autoincrement of last row at the insert
- How to get the value of autoincrement of last row at the insert
I have googled this problem one week and no thing useful I think am not using the correct word
I am using SQL Server 2008 with t-sql and my need is to optimise my function when I insert a new row.
I have a table with first column is the key of integer autoincrement type and other columns are just for information
When we do an insert, SQL Server increments the key automatically and I have to do a select max to get the value, so is there a way like a global variable like @@IDENTITY or a function to avoid the begin end transaction and select max
Use SCOPE_IDENTITY:
-- do insert
SELECT SCOPE_IDENTITY();
Which will give you:
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.
Just ran the code:
INSERT INTO Persons (FirstName) VALUES ('Joe');
SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;
and it also works!
What about this for last auto increment value
SELECT IDENT_CURRENT('tableName')-IDENT_INCR('tableName');
If you are using MySQL you get the auto increment ID of the last insert with:
In my case I had to use @@Identity, because I was inserting into a view. It seems that SCOPE_IDENTITY only works for ones you have explicitly created.
See here:
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
@@IDENTITY will return the last identity value entered into a table in your current session.
While @@IDENTITY is limited to the current session, it is not limited to the current scope.
If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
原文出處： sql - How to get the value of autoincrement of last row at the insert - Stack Overflow
|
|
|
|
|
冷日
(冷日) |
發表時間：2019/7/29 13:33 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Best way to get last identity inserted in a table
- Best way to get last identity inserted in a table
Which one is the best option to get the identity value I just generated via an insert? What is the impact of these statements in terms of performance?
SCOPE_IDENTITY()
Aggregate function MAX()
SELECT TOP 1 IdentityColumn FROM TableName ORDER BY IdentityColumn DESC
Use SCOPE_IDENTITY() if you are inserting a single row and want to retrieve the ID that was generated.
CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));
INSERT #a(x) VALUES('a');
SELECT SCOPE_IDENTITY();
Result:
Use the OUTPUT clause if you are inserting multiple rows and need to retrieve the set of IDs that were generated.
INSERT #a(x)
OUTPUT inserted.identity_column
VALUES('b'),('c');
Result:
and why this is best faster option?
Performance aside, these are the only ones that are guaranteed to be correct in the default isolation level and/or with multiple users. Even if you ignore the correctness aspect, SQL Server holds the inserted value in SCOPE_IDENTITY() in memory, so naturally this will be faster than going and running your own isolated query against the table or against system tables.
Ignoring the correctness aspect is like telling the mailman he did a good job delivering today's mail - he finished his route 10 minutes faster than his average time, the problem is, none of the mail was delivered to the right house.
Do not use any of the following:
@@IDENTITY - since this can't be used in all scenarios, for example when a table with an identity column has a trigger that also inserts into another table with its own identity column - you will get the wrong value back.
IDENT_CURRENT() - I go into detail about this here, and the comments are useful reading as well, but essentially, under concurrency, you will often get the wrong answer.
MAX() or TOP 1 - you would have to protect the two statements with serializable isolation in order to ensure that the MAX() you get isn't someone else's. This is much more expensive than just using SCOPE_IDENTITY().
These functions also fail whenever you insert two or more rows, and need all the identity values generated - your only option there is the OUTPUT clause.
Apart from performance, they all have rather different meanings.
SCOPE_IDENTITY() will give you the last identity value inserted into any table directly within the current scope (scope = batch, stored procedure, etc. but not within, say, a trigger that was fired by the current scope).
IDENT_CURRENT() will give you the last identity value inserted into a specific table from any scope, by any user.
@@IDENTITY gives you the last identity value generated by the most recent INSERT statement for the current connection, regardless of table or scope. (Side note: Access uses this function, and thus has some issues with triggers that insert values into tables with identity columns.)
Using MAX() or TOP 1 can give you entirely wrong results if the table has a negative identity step, or has had rows inserted with SET IDENTITY_INSERT in play. Here's a script demonstrating all of these:
CREATE TABLE ReverseIdent (
id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
data char(4)
)
INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')
SELECT * FROM ReverseIdent
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000
SET IDENTITY_INSERT ReverseIdent ON
INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')
SET IDENTITY_INSERT ReverseIdent OFF
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005
Summary: stick with SCOPE_IDENTITY(), IDENT_CURRENT(), or @@IDENTITY, and make sure you're using the one that returns what you actually need.
原文出處： sql server - Best way to get last identity inserted in a table - Database Administrators Stack Exchange
|
|
|
|
