Identity Values

Question

Identity Values

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 1

Thanks Roddy and Tom..

Tom,

Can you explain actually what is happening in background while executing DBCC Checkident(). How identity values are maintained by sql server?

It will be very useful for everyone to understand it in detail.

--
Dineshbabu
Desire to learn new things..

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Dineshbabu (1/24/2013)
Thanks Roddy and Tom..
Tom,
Can you explain actually what is happening in background while executing DBCC Checkident(). How identity values are maintained by sql server?
It will be very useful for everyone to understand it in detail.

Dineshbabu,

I don't actually know the internal implementation details for SQL server. However, I have done something similar in the distant past, and can describe a model for what is happening which may help your understanding (as long as you don't confuse the model with the real thing - SQL Server may achieve the same behaviour using a different underlying mechanism). I don't have time to do that just now, as visitors are due to show up in a five minutes and I have to prepare, but I' will try to remember and postan explanation later today or early tomorrow.

Tom

@Cassie SSC Eights! Points: 890 More actions · Answer 3

@Cassie

SSC Eights!

Points: 890

January 25, 2013 at 2:03 pm

#1581216

++1 Great, easy and nice.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Dineshbabu (1/24/2013)
Tom,
Can you explain actually what is happening in background while executing DBCC Checkident(). How identity values are maintained by sql server?
It will be very useful for everyone to understand it in detail.

As I said before, I can't actually describe that because I don't know the internals; but it is fairly easy to produce a model that gives the right behaviour. It's not terribly complicated, but the documentation isn't really adequate - I think much is left for people to find out the hard way. Anyway, I said I'd try to write up a model and post it, so here is a possible model for the behavious of identity values. Be very clear: this is not a description of how SQL Server does it - it's just a description of a process that would deliver the observed behaviour; it almost certainly does something completely different to this internally, as there are lots of ways of getting the same effect. This model uses two state variables - current identity value and prospective_identity value - in addition to the constants (seed and increment). The part dealing with dbcc checkident is pretty simple, a lot simpler than the part dealing with insert. I've left out the reporting bits of dbcc checkident, because the real issue was the meaning of RESEED.

An identity column in a table has a number of attributes:

Declared seed: value set on creation of the column and can not be changed.

Declared increment: value set on creation of the column and can not be changed.

Current identity value: set null on creation of the identity column; its value changes quite often.

Prospective identity value: set null on creation of the identity column. value can be set by a dbcc checkident call; as soon as it is used it is set back to null. It can be overwritten by a DBCC CHECKIDENT call, but other than that it can only change to null.

The operations on the table that affect these attributes are creating the table with the column, adding the column if it was not created with the table or has previously been dropped, dropping the column, calls to dbcc checkident, inserting rows, and truncating the table.

creating the column while creating the table: the declared seed and declared increment are taken from the column definition in the create table statement. The current identity value and prospective identity value both set null.

adding the column to an existing table: if there are no rows in the table, the actions is as for creating the column at the same time as the table. If there are rows, that action is followed by assigning an identity value to each row, the first value being the declared seed and each subsequent value being obtained by adding the declared seed to the previous value; the Current identity value is given the value of the last assigned identity value.

dropping the column: all the identity attributes are discarded.

truncating the table: the current identity value and the prospective identity value are set to null.

dbcc checkident with RESEED and a supplied value: the supplied value is checked for validity, and if this fails the call exits having done nothing but this check (the check is that the sign of the value obtained by subtracting the declared seed from the parameter value is the same as the sign of the declared increment; also, there must be no row in the table where the parameter value lies between the declared seed and the identity value in that row). If the check succeeds, the prospective identity value is overwritten with the parameter's value.

dbcc checkident with RESEED and no supplied value: if there are any rows in the table with an acceptable identity value (the acceptability check for an identity value in a row is that the value obtained by subtracting the declared seed from the value in the row has the same sign as the declared increment and, if the prospective identity value is not null, the identity value in the row must not lie between the declared seed and the prospective identity value) the call exits with no effect; if such rows do exist, the prospective identity value is set to the identity value in the one of them whose value is most distant from the declared seed value.

inserting a row without identity_insert being on: if the prospective identity value and the current identity value are both null, the row identity and the current identity value are both set to the declared seed value. If the current identity value is not null and the prospective identity value is null, the row is given the sum of the current identity value and the declared increment as its identity value, and this identity value also overwrites the current identity value. If there is a prospective identity value and no current identity value, the row is given the prospective identity value as its identity value, the current identity value is set to the prospective identity value, and the prospective identity value is set to null. If both the prospective identity value and the current identity value are not null, the row is given the sum of the prospective identity value and the declared increment as its identity, which also overwrite the current identity value, and the prospective identity value is set to null. These changes to prospective identity value and current identity value are not transactional - rolling back the current transaction won't reverse them. When multiple rows are inserted, the end result (apart from performance) is as if each had been inserted separately.

inserting a row with identity_insert on: Action depends on the identity value supplied in the insert statement. If this is not an acceptable identity value (the sign of the difference between it and the declared seed is the same as the sign of the declared increment, it does not lie between the declared seed and the current identity value if that not null, and it does not lie between the declared seed and the prospective identity value if that is not null) the row will be inserted, if the current identity value is null it will be given the value of the declared seed, and if the prospective identity value is the same as the declared seed it is set to null. If it is an acceptable value, the row will be inserted, the prospective identity value will set to null, and the current identity value will be given the value provided by in insert statement. As for insert without identity_insert the changes to the two identity attributes are not transactional and the logical effect of inserting multiple rows in a statement is the same as that of inserting them one at a time in separate statements.

Tom

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 5

Thanks Tom for spending your most valuable time and preparing such a detailed article.

I need to go through it twice or thrice and to test with samples to understand it in detail.

I found one thing odd -

adding the column to an existing table:
if there are no rows in the table, the actions is as for creating the column at the same time as the table. If there are rows, that action is followed by assigning an identity value to each row, the first value being the declared seed and each subsequent value being obtained by adding the declared seed to the previous value; the Current identity value is given the value of the last assigned identity value.

I think subsequent value will be obtained by adding declared increment to the previous value.

--
Dineshbabu
Desire to learn new things..

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Dineshbabu (1/28/2013)
Thanks Tom for spending your most valuable time and preparing such a detailed article.
I need to go through it twice or thrice and to test with samples to understand it in detail.
I found one thing odd -
adding the column to an existing table:
if there are no rows in the table, the actions is as for creating the column at the same time as the table. If there are rows, that action is followed by assigning an identity value to each row, the first value being the declared seed and each subsequent value being obtained by adding the declared seed to the previous value; the Current identity value is given the value of the last assigned identity value.
I think subsequent value will be obtained by adding declared increment to the previous value.

You are of course absolutely right.

Tom