Каково различие между Scope_Identity (), Идентификационные данные (), @@ Идентификационные данные, и Ident_Current ()?
Я знаю Scope_Identity(), Identity(), @@Identity, и Ident_Current() все получают значение столбца идентификационных данных, но я хотел бы знать различие.
Часть противоречия, которое я имею, - то, что они подразумевают под объемом в применении к этим функциям выше?
Я также любил бы простой пример различных сценариев использования их?
5 ответов
- The
@@identityfunction returns the last identity created in the same session. - The
scope_identity()function returns the last identity created in the same session and the same scope. - The
ident_current(name)returns the last identity created for a specific table or view in any session. - The
identity()function is not used to get an identity, it's used to create an identity in aselect...intoquery.
The session is the database connection. The scope is the current query or the current stored procedure.
A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.
So, normally you would use the scope_identity() function.
Good question.
@@IDENTITY: returns the last identity value generated on your SQL connection (SPID). Most of the time it will be what you want, but sometimes it isn't (like when a trigger is fired in response to anINSERT, and the trigger executes anotherINSERTstatement).SCOPE_IDENTITY(): returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc).IDENT_CURRENT(): returns the last identity value for a specific table. Don't use this to get the identity value from anINSERT, it's subject to race conditions (i.e. multiple connections inserting rows on the same table).IDENTITY(): used when declaring a column in a table as an identity column.
For more reference, see: http://msdn.microsoft.com/en-us/library/ms187342.aspx.
To summarize: if you are inserting rows, and you want to know the value of the identity column for the row you just inserted, always use SCOPE_IDENTITY().
To clarify the problem with @@Identity:
For instance, if you insert a table and that table has triggers doing inserts, @@Identity will return the id from the insert in the trigger (a log_id or something), while scope_identity() will return the id from the insert in the original table.
So if you don't have any triggers, scope_identity() and @@identity will return the same value. If you have triggers, you need to think about what value you'd like.
Область означает контекст кода, который выполняет оператор INSERT SCOPE_IDENTITY () , в отличие от глобальной области @@ IDENTITY .
CREATE TABLE Foo(
ID INT IDENTITY(1,1),
Dummy VARCHAR(100)
)
CREATE TABLE FooLog(
ID INT IDENTITY(2,2),
LogText VARCHAR(100)
)
go
CREATE TRIGGER InsertFoo ON Foo AFTER INSERT AS
BEGIN
INSERT INTO FooLog (LogText) VALUES ('inserted Foo')
INSERT INTO FooLog (LogText) SELECT Dummy FROM inserted
END
INSERT INTO Foo (Dummy) VALUES ('x')
SELECT SCOPE_IDENTITY(), @@IDENTITY
Дает разные результаты.
Scope Identity: Identity of last record added within the stored procedure being executed.
@@Identity: Identity of last record added within the query batch, or as a result of the query e.g. a procedure that performs an insert, the then fires a trigger that then inserts a record will return the identity of the inserted record from the trigger.
IdentCurrent: The last identity allocated for the table.