Identity values

Identity values

Post by Ben Wren » Sat, 15 Sep 2001 06:26:18



I have a question about the sys* tables. I have a column
in a table with the identity property set with a seed of 1
and an increment value of 1. Now, if have added some rows
to the table the identity value goes up by one each time a
row is added. When you delete the top 2 rows with the
greatest identity values and then add a new row it picks
up where the greatest row was, so if you have rows with
identity values 1 - 10 and then delete 9 and 10, then add
a new row you get 11 as the identity value even though 10
is gone from the table. Is there a system table that keeps
track of the identity value.
 
 
 

Identity values

Post by Simon Hughe » Sat, 15 Sep 2001 08:48:39


First, which version of MSSQL do you have?

Quote:> and an increment value of 1. Now, if have added some rows
> to the table the identity value goes up by one each time a
> row is added

No. The identity value is incremented when an INSERT statement is issued,
but the new value is generated before DRI checks, and before triggers fire.
So the new value may never be inserted, due to DRI check failure, or due to
a ROLLBACK in a trigger. Therefore, values generated with IDENTITY are
neither unique (unless used as a PK column, which is common; or enforced
with a unique index), nor sequential (if you need sequential values (and
it's often dubious why you would) then create a sequence table and relevant
triggers).

Quote:> When you delete the top 2 rows with the
> greatest identity values and then add a new row it picks
> up where the greatest row was

No. You're only seeing this because you have a small table and/or have a low
volume of INSERTs (no concurrency issues) - as described, identity is not
sequential.

Quote:> Is there a system table that keeps
> track of the identity value.

That depends on the version of MSSQL - there are several related functions.
Search on IDENTITY in Books Online for a start. In any case, as described
above, IDENTITY is neither unique nor sequential, so while you can retrieve
the curernt identity value within a transaction, you can never predict the
next 'valid' value.

HTH,

Simon


Quote:> I have a question about the sys* tables. I have a column
> in a table with the identity property set with a seed of 1
> and an increment value of 1. Now, if have added some rows
> to the table the identity value goes up by one each time a
> row is added. When you delete the top 2 rows with the
> greatest identity values and then add a new row it picks
> up where the greatest row was, so if you have rows with
> identity values 1 - 10 and then delete 9 and 10, then add
> a new row you get 11 as the identity value even though 10
> is gone from the table. Is there a system table that keeps
> track of the identity value.


 
 
 

1. Generating identity values without IDENTITY keyword

Hi!!
I want to insert several rows in an existent table. This table doesn't use
the IDENTITY keyword in the primary key field, but I would like to fill it
with increasing integer numbers.
The inserted rows come from a query from another table, and I found that
you can't assign a variable inside of a query to do something like this:


insert into MyTable (pk, field1, field2, ...)

from MyOtherTable

I have used the IDENTITY function (which is diferent from an Identity
property in a table), but then I have to use the INTO clause in the select
and create a temporary table.


into #TempTable
from MyOtherTable

insert into MyTable (pk,field1,field2, ...)
select *
from #TempTable

Is there any way to skip the temporary table creation?
--
Mario De Sousa Barrera
Eniac, C.A.
Caracas, Venezuela

2. - add column to publication -> column doesn't synchronize

3. Identity columns and invalid identity values.

4. How to use ROUTINE_COLUMNS

5. Retrieve Identity value (select @@identity...)

6. HELP: program jumps through VALID clause

7. Attempt to insert duplicate identity values in table (has identity and key fields)

8. Setup Suggestions

9. Setting the Next identity value on an IDENTITY column

10. determining the next value of an identity value to be assigned

11. getting identity value after insert

12. Incorrect Identity value

13. recycling identity values