April 5, 2009 at 4:29 pm
I have a huge list of 50 million or so. here is the basic schema
id bigint IDENTITY(1,1),
sn varchar(50)
Create primary key pk_id on table(id)
Create unique index ui_sn on table(sn)
this is a brand new table, I have inserted over 50 million records into this table and it seemed the ID column was correct.
But for some reason, over time, after doing alot of inserts into the table (into the field sn) that my ID gets out of order.
Like for example select id from table where id > 4999989 and < 50000000
i would see:
4999990
4999996
4999997
4999999
When i would expect to see 10 results in order.
I can assure you that i have run NO deletes what so ever, so why is my idenity column not staying in order????
April 5, 2009 at 5:36 pm
gaps in an identity are normal. they happen when an error occurs and a transaction gets rolled back; although a transaction would not get committed, the incrementing of an identity still occurs.
if this is happening, you should check your code to see why an insert got rolled back.
Lowell
April 5, 2009 at 11:37 pm
hi as per lowell told you need to check your code first ..and another thing if your inserted records have you count are they same...as in source table...may be when transactions rollback or like that you may face problem...try to insert data into slots...by putting where clause...
Raj Acharya
April 6, 2009 at 5:16 am
Hi,
Also there could be a posibility that few records must be deleted at the source table at some point of time. Later when this table gets exported to any destination table, then that table will also miss those identity values...
Just an example:
1. create table iden (Number int identity(1,1), Name char(10))
insert into iden values ('AAA')
insert into iden values ('BBB')
insert into iden values ('CCC')
insert into iden values ('DDD')
insert into iden values ('EEE')
select * from iden
1AAA
2BBB
3CCC
4DDD
5EEE
Now let us delete from record from this:
delete iden where Name like 'BBB'
insert into iden values ('XXX')
select * from iden
1AAA
6XXX
3CCC
4DDD
5EEE
2. Now we will create a new table and import the data from teh above table.
create table idencopy2 (Number int identity(1,1), Name char(10))
SET IDENTITY_INSERT idencopy2 ON
Insert into idencopy2(Number,Name) (select Number,Name from iden)
select * from idencopy2
1AAA
6XXX
3CCC
4DDD
5EEE
select * from idencopy2 where number between 1 and 3
1AAA
3CCC
So if the same example can be applied on 50 million records and expecting few more records getting deleted on the source table.. The scenario explained by you is very much possible..
-Rajini
April 6, 2009 at 9:28 am
rajiniforu (4/6/2009)
Hi,Also there could be a posibility that few records must be deleted at the source table at some point of time. Later when this table gets exported to any destination table, then that table will also miss those identity values...
Just an example:
1. create table iden (Number int identity(1,1), Name char(10))
insert into iden values ('AAA')
insert into iden values ('BBB')
insert into iden values ('CCC')
insert into iden values ('DDD')
insert into iden values ('EEE')
select * from iden
1AAA
2BBB
3CCC
4DDD
5EEE
Now let us delete from record from this:
delete iden where Name like 'BBB'
insert into iden values ('XXX')
select * from iden
1AAA
6XXX
3CCC
4DDD
5EEE
2. Now we will create a new table and import the data from teh above table.
create table idencopy2 (Number int identity(1,1), Name char(10))
SET IDENTITY_INSERT idencopy2 ON
Insert into idencopy2(Number,Name) (select Number,Name from iden)
select * from idencopy2
1AAA
6XXX
3CCC
4DDD
5EEE
select * from idencopy2 where number between 1 and 3
1AAA
3CCC
So if the same example can be applied on 50 million records and expecting few more records getting deleted on the source table.. The scenario explained by you is very much possible..
-Rajini
yes i have noticed that identity values seem to cary from table to table. So usually what i have to do is create a complete new table by hand and insert data manually.
April 6, 2009 at 9:29 am
why would an insert be rolled back? its just a basic insert
April 6, 2009 at 9:38 am
we don't know...it could be something in your code...maybe you have ignore_dups in your import?
if it's from an application,say from the client-server or web and a session times out....starting a transaction and getting disconnected...there's lots of potential reasons before we ever look a tthe code itself.
maybe you enter parent/child data, and if something is missing in the child data, you rollback?
rajiniforu had a better explanation:
inserts could be working 100%, but some other process is deleting records, based ob code or business needs?
Lowell
April 6, 2009 at 10:03 am
Lowell (4/6/2009)
we don't know...it could be something in your code...maybe you have ignore_dups in your import?if it's from an application,say from the client-server or web and a session times out....starting a transaction and getting disconnected...there's lots of potential reasons before we ever look a tthe code itself.
maybe you enter parent/child data, and if something is missing in the child data, you rollback?
rajiniforu had a better explanation:
inserts could be working 100%, but some other process is deleting records, based ob code or business needs?
i can assure no deletes ever happened on this table in any way shape or form ever
April 6, 2009 at 2:38 pm
xgcmcbain (4/6/2009)
Lowell (4/6/2009)
we don't know...it could be something in your code...maybe you have ignore_dups in your import?if it's from an application,say from the client-server or web and a session times out....starting a transaction and getting disconnected...there's lots of potential reasons before we ever look a tthe code itself.
maybe you enter parent/child data, and if something is missing in the child data, you rollback?
rajiniforu had a better explanation:
inserts could be working 100%, but some other process is deleting records, based ob code or business needs?
i can assure no deletes ever happened on this table in any way shape or form ever
By definition, identity has gaps, check BOL. If you need sequential id's without gaps, identity is not the way.
April 6, 2009 at 4:17 pm
xgcmcbain (4/6/2009)
why would an insert be rolled back? its just a basic insert
Because you have duplicates in your import files 🙂
(See post http://www.sqlservercentral.com/Forums/Topic690671-338-1.aspx)
Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...
April 6, 2009 at 6:29 pm
Tim Wilson-Brown (4/6/2009)
Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...
Actually SET IDENTITY_INSERT ON would let you insert values into column that has identity built. Is that what you mean ?
Source : Click here
~ IM
April 6, 2009 at 7:17 pm
sayfrend (4/6/2009)
Tim Wilson-Brown (4/6/2009)
Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...
Actually SET IDENTITY_INSERT ON would let you insert values into column that has identity built. Is that what you mean ?
Source : Click here
~ IM
No, I meant what I said 🙂
The current Identity value stays incremented, even if the transaction that used it is rolled back.
xgcmcbain was complaining about non-unique inserts failing in another post about the same table: http://www.sqlservercentral.com/Forums/Topic690671-338-1.aspx.
See the explanation in BOL: http://msdn.microsoft.com/en-au/library/ms175098(SQL.90).aspx under 'Remarks'.
Edited to give SQL 2005 BOL reference (was SQL 2008 BOL reference).
April 6, 2009 at 7:22 pm
Tim Wilson-Brown (4/6/2009)
xgcmcbain (4/6/2009)
why would an insert be rolled back? its just a basic insertBecause you have duplicates in your import files 🙂
(See post http://www.sqlservercentral.com/Forums/Topic690671-338-1.aspx)
Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...
is there a way to turn that off?
April 6, 2009 at 8:05 pm
xgcmcbain (4/6/2009)
Tim Wilson-Brown (4/6/2009)
xgcmcbain (4/6/2009)
why would an insert be rolled back? its just a basic insertBecause you have duplicates in your import files 🙂
(See post http://www.sqlservercentral.com/Forums/Topic690671-338-1.aspx)
Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...
is there a way to turn that off?
What do you want to turn off:
* Rollback of Duplicate Inserts, or
* Missing Identity Values from Failed Transactions?
You can design your insert query to filter out duplicates.
That will avoid both issues, and is a better design than other alternatives.
Or you can set the unique index to ignore duplicate inserts (IGNORE_DUP_KEY = ON).
This will avoid failures on duplicate inserts, but I'm not sure if the identity values will still be used.
This is a poor design because it just hides the problem - in particular, it doesn't allow you to update duplicate rows with later values.
Or you can run DBCC CHECKIDENT('table_name',RESEED) after every failed insert.
This will allow you to reuse the 'lost' identity values from the most recent insert.
But again this is a poor design because it just hides the problem - and could still allow gaps when rows are deleted, or a successful insert happends before you can reseed the table.
Can you explain why your identity values need to have no gaps?
April 6, 2009 at 11:17 pm
jgrubb (4/6/2009)
By definition, identity has gaps, check BOL. If you need sequential id's without gaps, identity is not the way.
Ummm.... you're gonna have to show the BOL link for that one. It is NOT by definition that an IDENTITY column will have gaps. They only occur if a rollback or deletion has occurred. From BOL...
Note:
If an identifier column exists for a table that has frequent deletions, gaps can occur between identity values. Deleted identity values are not reused. To avoid such gaps, do not use the IDENTITY property.
... and, there are very, very few good reasons to avoid gaps.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply