SQL Server Performance Forum – Threads Archive

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

---

I can only answer your first question. I’m not into replication at all.
The next row inserted will get 100,001. SQL Server will not recycle any "unused" numbers. Over time itÄs almost impossible to maintain a perfect sequence using the IDENTITY property. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

why d u say :
"Over time itÄs almost impossible to maintain a perfect sequence using the IDENTITY property.
"?

---

Say for example you have the following values in a column with an identity: 1
2
3
4 The next identity should be 5, right? Now if you delete the row with identity value 4, the next identity is still 5. In other words, SQL Server keeps a track of identity values used (even if they are deleted). In your table, I suspect that values 60001 to 60159 existed at some point but were subsequently deleted. This is why it is impossible to maintain identity values over time, as Frank suggested. Although I guess if you never ever delete from a table then it is possible. Regarding the Not For Replication option, this is used in replication where you want the identity value to be replicated (as is) to the subscriber. In other words, rows at the subscriber are not assigned an identity value based on the next available identity value – they are effectively copied from the publisher. If you manually add a row at the subscriber (if you have updating subscriptions enabled) then the identity value will increment as normal so you have to make sure you assign valid ranges when you set up replication. Hope that helps
Karl Grambow www.sqldbcontrol.com

---

The deleted identity values can be inserted only using SET IDENTITY_INSERT tableName ON Madhivanan Failing to plan is Planning to fail

---

can you explain what execlly mean’s publisher and subscriber

---

You have a lot of information in BOL about publisher an subscriber.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.

---

]]>