Understanding Your Identity
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/understandingyouridentity.asp
-
A good article to show how identity property works.
I think thats how it should work. In case of concurrent INSERT(s) it will always generate a unique value and yes IDENTITY values can become 'jumpy' because of rollbacks but its Ok.
-
If Identities were allocated as part of a transaction, then the allocation of identities for any other connections would be locked out until the end of the transaction.
The concurrency and performance implications of this don't bear thinking about.
-
Very good article. Stuff like this is always well worth keeping in mind. especially seeing as sybase and (if memory serves) oracle behave in the same way
nice one
-
I think I am missing something, here. Perhaps it is the syntax of your article and my understanding of the English language. I always believed that, no matter what you do with a row on a table containing an IDENTITY field, a new row would AUTOMATICALLY have a new ID anyways, based on the seed and increment values, unless the SET IDENTITY_INSERT [table_name] ON command it run. I ran both cases you outlined in the against SQLServer 2000 Developer Edition and SQL Server 7.0 Standard Edition. Both cases on both servers returned identical results. I was expecting this to be the case. Was it supposed to be so?
-
Good article, I hadn't thought that much about it - we don't care that the Identity columns are sequential. Unique we do care about. Your quote from MS that "Identity columns are not guaranteed to be sequential and unique" bothers me. They will stay unique, right? Please? Pretty please?
Student of SQL and Golf, Master of Neither -
Wow, thanks for the responses and sorry for being so late in getting to them. Here's a few thoughts.
I got a message from one person expressing this was not a bug due to concurrency issues. I tend to agree. I think this was an easy performance boost at some point be decoupling the indentity generation from the transaction. Makes sense. You'd hate for one person to be waiting on another for an insert.
As far as does this always generate a new value? Not necessarily. It generates a value based on a seed and an increment. If you never use SET IDENTITY_INSERT or DBCC CHECKIDENT, then, yes you will alwyas have a new value. However, these commands can cause duplicate values to be generated or entered. Therefore you need to keep this in mind and use an index if you want to guarentee uniqueness.
Steve Jones
-
Great article. I've been told that Oracle reponds in the same manner. How about other db's?
-
Thanks Steve. You always have such clear precise answers. I knew about SET IDENTITY_INSERT, seems that would only apply when one wants to deliberately copy data from one server to another and keep the same values on both or something similar. I didn't know about DBCC CHECKIDENT and don't see where I care currently. I can now sleep peacefully.
Student of SQL and Golf, Master of Neither -
You are welcome. Check out DBCCCheckIdent. It will allow you to reseed the identity value (useful when trying to synch two servers), especially in a dev environment. It also lets you check the current identity seed.
Steve Jones
-
It is fabulous. Coincidently I experienced this bug 3-4 days back but I was not alert enough to focus on it consider it as a bug. That was the reason why your article heading took my attention. Thanks a tonnnnn.
Vishal Varshney
-
You are welcome. Glad it caught some eyes, but I'm not sure it's a bug anymore. I tend to this this is by design to increase performance.
Steve Jones
-
I had already noticed this behavior, and do not agree that it should be considered a bug.
Consider the way SQL Server "AFTER" triggers and ROLLBACK work: the trigger fires after the data have been stored but before the final COMMIT. From the IDENTITY property's perspective, it did its job when it supplied the new value (based on seed and increment) for the rolled-back row... it didn't know you were planning a rollback, after all. It gave you the next increment, only your trigger decided not to use it.
If memory serves, I don't think you would have gotten any different result had you used a sequence in Oracle: The sequence will give you the "next" value to use as you see fit. If you decide to roll it back after an INSERT, fine, but your "next" sequence value will have moved on.
If SQL Server were to make the identity part of the actual transaction, it would probably have to change a lot of things under the hood, e.g., embrace the IDENTITY generator with a series of locking mechanisms, increasing the odds of deadlocks.
-
-
quote:
If Identities were allocated as part of a transaction, then the allocation of identities for any other connections would be locked out until the end of the transaction.The concurrency and performance implications of this don't bear thinking about.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply