Identity in SQL Server

Source: Internet
Author: User

Https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide

    • The function returns the last identity of the created in the @@identity same session.
    • The scope_identity() function returns the last identity created in the same session and the same scope.
    • The last identity of the ident_current(name) returns created for a specific table or view in any session.
    • identity()The function is not used to get an identity, it's used to create an identity in a select...into query.

The session is the database connection. The scope is the current query or the current stored procedure.

A situation where scope_identity() @@identity the and the functions differ are if you had a trigger on the table. If you had a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function would Return the identity created by the query, while the @@identity function would return the identity created by the trigger.

So, normally your would use the scope_identity() function.

Https://stackoverflow.com/questions/13399565/is-there-any-way-to-use-scope-identity-if-using-a-multiple-insert-statement

No, only SCOPE_IDENTITY() gives the one , latest inserted IDENTITY value. Could check out the clause of OUTPUT SQL Server ....

DECLARE @IdentityTable TABLE(SomekeyvalueINT, newidentityINT)INSERT  into [MyTable]OUTPUT Inserted.keyvalue, Inserted.id into @IdentityTable(Somekeyvalue, newidentity)VALUES('1'), ('2'), ('3')

Once you ' ve run your INSERT statement, the table variable would hold "some key value" (for your, to identify the row) and the Newly inserted ID values for each row inserted. Now go crazy with this! :-)

where inserted is related to output.

Identity in SQL Server

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.