Incorrect @ identity for obtaining the latest ID in sqlserver

Source: Internet
Author: User

A netizen asked me a question about @ identity. There is a stored procedure in his database with an insert statement, and then immediately use select @ identity to obtain the newly inserted id value. This is usually correct, however, the problem is that the ID actually reaches more than 1 million, and only a small value (more than 30) returned by select @ identity is confusing.

I didn't quite understand it at the beginning. I asked him not to worry about @ identity, but to insert a new record with the ID. He said that the number of IDS is as large as more than 1 million. Not dozens of small ones. I helped him analyze the possibility, whether identity reached the maximum limit, and the maximum identity was 2 to the 64 power. He said that the maximum value of Int Is 2,147,483,647, and his ID is still earlier than overflow, but return @ identity is a very small value (only dozens ). Then I helped him analyze whether the id value was reset? He said no. He said that the newly inserted ID is more than 1.7 million. I have learned a little about it. I suggest using the scope_identity (table_name) function to retrieve the identity of the specified table. We have previous experiences. @ identity and identity_current () are not ideal because they are affected by the range of the current table. A trigger usually causes @ identity to fail to take the correct value, as does identity_current. Fortunately, the scope_identity (table_name) function is available. In this case, you can only use this. Then he remembered that his table had a trigger. Oh, it's a little late. I suggest that you do not use a trigger. This can be avoided. He said that the trigger can be replaced by other methods. He will try using the scope_identity (table_name) function and remove the trigger.

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.