This is a follow up to the post titled "Getting the ID field after an Insert". I recently saw someone struggle with this because of an easily made syntax error.
Consider this code:
Follow up post on doing this in MS Access.
Do you see the "error"? It's the phrase "FROM users". It's pretty easy to think you might need that "from users". After all, you are trying to retrieve the Identity field that was recently created by the system in the users table. The thing to remember is that @@Identity does not exist in the users table. It is a scope variable that exists inside this transaction.
Here's the other thing to keep in mind, this code is syntactically correct. The SQL server will not complain that this is in error. Why? Because you can select a constant as if it were from a table. For example, you can do the following:
Name username Mark sjones Mark mJohnson Mark tSlovinsky ...and so on....Now consider the code above. If the value of @@Identity IS 30 and the table has 30 rows you will get back thirty rows of the number 30.
The thing about this error is that you may never realize it's there. Your code may work perfectly well with the syntax above. One of the uses of doing this type of identity retrieval is to insert something in a related table along with the new ID. So you write code like this:
If you make this little error, your code may work exactly as expected during development. It may even work very well during load testing unless you create custom scripts capable of entering hundreds of records. The problem is that if your table (users in the this case) grows too large your code will bog down and eventually fail. It will not scale. Every insert will return a large array of numbers the size of the table. 30 or 40 thousand rows and you will start noticing the decline. It will be very hard to debug as well. Nothing in the logs will help. Nothing on the database will help. You will have to notice the record count in the debug - something you may overlook on an insert query. So watch out!