insert lines into a db with Identity
-
i have inserted into a table with a column called id defined as Identity(Yes) line from a file.
the line in the file in the id column had a number in each row
so now in the table i have
1 some data
2 some data
....
100000 some data
the things is that some lines were missing , for example :
rows
60000
...
60159
so there is a "hole" i nthe table's id at this numbers
my question is like this :
1)if now i insert a new row will the id column will get the value 100,001 or it will recive the number of a missing line?
2) what is the exect diffrence between Identity (Yes) and Yes (Not For replication)?
thnaks in advance
peelg
-
1) You can check what the current identity value is by doing:
select ident_current('table_name')
The next value will be the higher increment i.e. the next value.
The missing values (aka holes) in the list do not get re-used automatically unless you take care of re-sequencing by using a script.
2) Check the "NOT FOR REPLICATION" option in BOL. It states:
"The NOT FOR REPLICATION option is used by Microsoft® SQL Server™ 2000 replication to implement ranges of identity values in a partitioned environment. The NOT FOR REPLICATION option is especially useful in transactional or merge replication when a published table is partitioned with rows from various sites."
Normal identity columns would behave like they do i.e. for auto-incrementing the values. The not for replication option has to do with how the identity values are maintained by the replication agent when replicating the row to the subscriber.
-
The missing values (aka holes) in the list do NOT get re-used automatically unless you take care of re-sequencing by using a script.
Also this is something that is irrelevant 99.9% of the time. Those holes mean nothing to the users as this proprietary data is almost always used only for data retrieval performance improvement.
-
It was a typo on my part...I meant to write do NOT get re-used automatically unless you take care of re-sequencing by using a script...thanks for correcting it.
-
NP... already done that error, and when left unnoticed it can cause some serious problems
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply