Go SQL three get self-growing ID method

Source: Internet
Author: User

Three ways to get a self-growing ID in SQL Server This feature is more commonly used, so write it down in case you forget it.

Scope_identity

Returns the last identity value in the identity column inserted into the same scope. A scope is a module-a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Ident_current returns the identity value that was last generated for any session and for the specified table in any scope. This function requires a variable that is a value of the table name, which means that although it is not restricted by the session and scope, it is limited by the table.

@ @IDENTITY

Returns the last inserted identity value.

Experience: plus things to deal with, two functions a variable has no essential difference. No matter handling two functions one variable is affected differently by other sessions and scopes.

You can also use triggers to get them.
Create trigger My_trig on article for insert
As
Select ArticleID from inserted
Go

Always use @ @identity to get the identity value of the last record inserted into the table, and recently found that this method is not reliable in some cases, let's look at two conceptual scopes: in SQL Server scope is a module-stored procedure, trigger, function or batch processing Session: All contextual information generated by a user connection (online book does not find the concept of a session, as referred to in the context of the conversation)

Same point: Returns the last inserted identity value difference: @ @identity: Returns the last identity value of the current session, not limited to a specific scope; Ident_current (' tablename '): Returns the last identity value generated from any session, in the specified table in any scope ; Scope_identity: Returns the last identity value that is generated by any table in the current session's scope.

Related Article

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.