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.