identity value gaps

Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.This is not a problem. The identity does not guarantee that you will get no
gaps. In the event that an INSERT occurs in a transaction, the identity it
would have taken does not get re-used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.|||"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In production system we have a table which takes orders, order-id is
defined
> with identity(1, 1) as part of table defination. Problem is that most of
the
> time they see order-id's in sequence, but some time they see gaps in
> order-id's , so busines rep asking me what happened with gaps. I asked
> developers, are there any deletes or is it transactionalised. But they
told
> me that it is very simple application, have one straight forward Insert
> command. I am wondering how can i track id gaps. I dont want to go with
> alternate solution, only i need is an answer to business rep why there are
> gaps. It is happening once in a month. Let me know if you need more info
>
Basically any time an IDENTITY is generated, it's "used up".
So if someone generates an order, but say cancels it before it's completed,
that IDENTITY value is still used up.
Identity columns should NOT be used in almost all cases for an externally
used identifier for this and other reasons.
Imagine for example you BCP the data out, truncate the table and then BCP it
in (very rare to do, but not unheard of).
Your data will NOT have gaps now (or if it does, they may be different.)
This means all your Order-IDs are now "wrong".
> Thanks,
> Subbu.
>

identity value gaps

Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.This is not a problem. The identity does not guarantee that you will get no
gaps. In the event that an INSERT occurs in a transaction, the identity it
would have taken does not get re-used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.|||"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In production system we have a table which takes orders, order-id is
defined
> with identity(1, 1) as part of table defination. Problem is that most of
the
> time they see order-id's in sequence, but some time they see gaps in
> order-id's , so busines rep asking me what happened with gaps. I asked
> developers, are there any deletes or is it transactionalised. But they
told
> me that it is very simple application, have one straight forward Insert
> command. I am wondering how can i track id gaps. I dont want to go with
> alternate solution, only i need is an answer to business rep why there are
> gaps. It is happening once in a month. Let me know if you need more info
>
Basically any time an IDENTITY is generated, it's "used up".
So if someone generates an order, but say cancels it before it's completed,
that IDENTITY value is still used up.
Identity columns should NOT be used in almost all cases for an externally
used identifier for this and other reasons.
Imagine for example you BCP the data out, truncate the table and then BCP it
in (very rare to do, but not unheard of).
Your data will NOT have gaps now (or if it does, they may be different.)
This means all your Order-IDs are now "wrong".

> Thanks,
> Subbu.
>

Identity seed reset in SQL table

I have a test database that is being moved to the production server. Currently in one of the tables I have an identity seed for each record. Is there a way to reset it back to zero. I have deleted all my records but it still doesnt work, and I dont want to create a new table.

ThanksCheck out DBCC CHECKIDENT. This will do what you need.

FromSQL Server 2000 Books Online:

C. Force the current identity value to 30
This example forces the current identity value in the jobs table to a value of 30.

USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO

Terri|||I believe that if you issue a TRUNCATE TABLE command on the table, the identity seed will be reset to its original value.

Overall, a better strategy, since TRUNCATE TABLE is much faster than the corresponding delete statement...|||how do I do this?|||From within SQL Query Analyzer:

TRUNCATE TABLE tablename
GO

Be careful. This will delete all of the records in your table, swiftly and immediately, upon hitting that magic F5 key (or upon hitting the run arrow).|||mmmm truncate table... :) I've scared many of people with that.

Best thing to do is put that thing in a transaction just in case.

identity ranges on republished restore of db

Hi folks,
We are having issues with restores of our production databases into our
test environments.
Our production environment is SQL server 2000 sp3 with merge
replication with 3 publications using automatic identity range
management.
The problem we're having is that when we try to restore a production
backup into a test environment and republish the database - inserts on
the server are failing due to the identity ranges being full. We run
the sp_adjustpublisheridentityrange stored procedure but this produces
inconsistent results. Sometimes it fixes the problem and allows inserts
to proceed but other times it doesnt
What we see happening is that it might fix the first table the range is
full on but when we try an insert on another table we get the range
full message again.
Its causing us major grief for an app we have thats trying to merge
duplicate customers on our system. The merge process involves lots of
inserts server side as we make back up copies of the customers before
we try merging them etc.
i guess what we're after is a consistent way to set/reset and make sure
identity ranges are ok across the database once we republish it.
Any help would be greatly appreciated
Thanks,
Michael
This should be working. What you should do is run your merge agents at a
greater frequency. You might also want to change your subscriber ranges to
match the largest increment which might ever occur on your subscriber
between merge agent runs. So if your merge agent runs once a week, and you
do 1000 inserts on the subscriber at a max in this interval, tune your
subscriber range accordingly.
To address your problem
Run dbcc checkident('tablename')
Get the current value
then issue a select max(identititycolumn) from tablename and get the current
value in your table.
reseed for this value
run dbcc checkident('tablename',reseed, maxvalue)
The alter your check constraint
to do this issue sp_help tablename
look for the check constraint called something like
repl_identity_range_pub_84EB4FA5_28B9_4AD2_82CC_27 B1CE554D0D
Alter this constrainst for your new range.
Keep in mind when you blow a range, subsequent inserts fail, but the
identity value is incremented.
Also adjust the MSrepl_identity_range table in your distribution database
for this new range.
The merge agent polls sysmergearticles on your subscriber with each run to
see if any of them have automatic identity range management support. It
should adjust the identity ranges automatically with each run. I am not
exactly sure why this is not working for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117018628.625701.77440@.g47g2000cwa.googlegro ups.com...
> Hi folks,
> We are having issues with restores of our production databases into our
> test environments.
> Our production environment is SQL server 2000 sp3 with merge
> replication with 3 publications using automatic identity range
> management.
> The problem we're having is that when we try to restore a production
> backup into a test environment and republish the database - inserts on
> the server are failing due to the identity ranges being full. We run
> the sp_adjustpublisheridentityrange stored procedure but this produces
> inconsistent results. Sometimes it fixes the problem and allows inserts
> to proceed but other times it doesnt
> What we see happening is that it might fix the first table the range is
> full on but when we try an insert on another table we get the range
> full message again.
> Its causing us major grief for an app we have thats trying to merge
> duplicate customers on our system. The merge process involves lots of
> inserts server side as we make back up copies of the customers before
> we try merging them etc.
> i guess what we're after is a consistent way to set/reset and make sure
> identity ranges are ok across the database once we republish it.
> Any help would be greatly appreciated
> Thanks,
> Michael
>
|||Hi Hilary,
Thanks very much for the quick reply. We have a script which checks the
current ident for each table - - then compares this value to the max
column value in each table. If the max column value is above the seed
value it sets the seed value to be the max column value plus 1 doing a
run dbcc checkident('tablename',reseed, maxvalue+1)
do we also need to update the constraint on the table? and if so what
do you set the constraint values to be?
For example one of our tables is called ApplProduct
The max value in the identity column is 1881002
The current identity value is 12427472
The current range constraint on the table is
([ApplProd_ID] > 11490000 and [ApplProd_ID] < 13490000)
After we run our script to produce the new seed value is produces the
DBCC command below:
DBCC CHECKIDENT ('ApplProduct', RESEED, 18810003)
We dont alter the constraint... i would have thought the
adjustpublisheridentityrang=ADe sp might do that? If we need to alter
the constraint what should the new value be?
Again your help is much appreciated
Thanks,
Michael
|||You only need to update the constraint IF non replication processes will be
inserting the rows, i.e. if you or your app want to insert the rows on the
subscriber.
First off I would reseed your table to DBCC CHECKIDENT ('ApplProduct',
RESEED, 1881002)
Basically from what I see you have inserted or attempted to insert
12427472 -1881002 rows which the constraint has kicked back a mere 10546470
rows.
I'd alter the constraint for this range as well
([ApplProd_ID] > 1881000 and [ApplProd_ID] < 3000000)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117023368.665062.255950@.g47g2000cwa.googlegr oups.com...
Hi Hilary,
Thanks very much for the quick reply. We have a script which checks the
current ident for each table - - then compares this value to the max
column value in each table. If the max column value is above the seed
value it sets the seed value to be the max column value plus 1 doing a
run dbcc checkident('tablename',reseed, maxvalue+1)
do we also need to update the constraint on the table? and if so what
do you set the constraint values to be?
For example one of our tables is called ApplProduct
The max value in the identity column is 1,881,002
The current identity value is 12,427,472
The current range constraint on the table is
([ApplProd_ID] > 11490000 and [ApplProd_ID] < 13490000)
After we run our script to produce the new seed value is produces the
DBCC command below:
DBCC CHECKIDENT ('ApplProduct', RESEED, 18810003)
We dont alter the constraint... i would have thought the
adjustpublisheridentityrangXe sp might do that? If we need to alter
the constraint what should the new value be?
Again your help is much appreciated
Thanks,
Michael
|||Our insertions are happening on the server Hilary - does this make any
difference? Our inserts all use SCOPE IDENTITY to get their values so i
would have thought we'd be ok i.e. we'd be inserting in a replication
friendly way?
Thanks,
Michael
|||Hi folks,
It looks like we need to do a lot more around identity ranges for bulk
server side inserts. From our investigations it looks like our initial
server ranges on some tables are almost full. The laptop(subscriber)
ranges all seem to sit above the server range at the moment. The server
has a much larger range than any of the subscribers - 2 million plus
(to handle out initial migration and nightly imports from another
database). One solution might be to reset our server range on all the
tables to above the current max subscriber range. That would give us a
fresh start as it were in a new range and hopefully the level of server
side inserts wont be as large to blow the range again for some time
If we leave the ranges as is then we're likely to have to build some
sort of range checking into our inserts to check when the server range
is near its max and re adjust accordingly.
apart from leaving gaps in our indenties will reseting them upset
anything else in the db?
Cheers,
Michael
|||I'm having a problem understanding how you are using the scope_identity
property.
What I said holds for both inserts occurring at the publisher or subscriber.
Just make sure you adjust the publisher range accordingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117032974.032687.146510@.g44g2000cwa.googlegr oups.com...
> Our insertions are happening on the server Hilary - does this make any
> difference? Our inserts all use SCOPE IDENTITY to get their values so i
> would have thought we'd be ok i.e. we'd be inserting in a replication
> friendly way?
> Thanks,
> Michael
>
|||Possibly. One thing that worked for one client of mine was not to use
automatic identity range management and just travel to the publisher or
subscriber, issue a dbcc checkident('tablename',reseed,12312396490)
on an as needed basis.
We used large ranges and monitored to see when we were getting close to
being full.
What made this work for us is that we knew we weren't going to grow, and had
a good handle on where data was going to be inserted and how much.
So, it was basically the set it and forget it philosophy, although it
required some monitoring. Naturally after we got everything working and all
the bugs shaken out, management added 10 more subscribers.
Automatic Identity Range Management is a maintenance free way, scalable way
of partitioning and efficiently using identity ranges. With planning you
should be able to have it parcel out the identity range chunks you need. You
should by now have a handle on what the representative subscriber and
publisher ranges should be.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<micks_address@.yahoo.co.uk> wrote in message
news:1117100177.816930.108770@.z14g2000cwz.googlegr oups.com...
> Hi folks,
> It looks like we need to do a lot more around identity ranges for bulk
> server side inserts. From our investigations it looks like our initial
> server ranges on some tables are almost full. The laptop(subscriber)
> ranges all seem to sit above the server range at the moment. The server
> has a much larger range than any of the subscribers - 2 million plus
> (to handle out initial migration and nightly imports from another
> database). One solution might be to reset our server range on all the
> tables to above the current max subscriber range. That would give us a
> fresh start as it were in a new range and hopefully the level of server
> side inserts wont be as large to blow the range again for some time
> If we leave the ranges as is then we're likely to have to build some
> sort of range checking into our inserts to check when the server range
> is near its max and re adjust accordingly.
> apart from leaving gaps in our indenties will reseting them upset
> anything else in the db?
> Cheers,
> Michael
>
|||I've been following along this thread, as it sounds similar to an issue I'm
ranting about above ... one question in this thread: how should the check
constraint be altered?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oj8D31gYFHA.796@.TK2MSFTNGP09.phx.gbl...
> Possibly. One thing that worked for one client of mine was not to use
> automatic identity range management and just travel to the publisher or
> subscriber, issue a dbcc checkident('tablename',reseed,12312396490)
> on an as needed basis.
> We used large ranges and monitored to see when we were getting close to
> being full.
> What made this work for us is that we knew we weren't going to grow, and
> had
> a good handle on where data was going to be inserted and how much.
> So, it was basically the set it and forget it philosophy, although it
> required some monitoring. Naturally after we got everything working and
> all
> the bugs shaken out, management added 10 more subscribers.
> Automatic Identity Range Management is a maintenance free way, scalable
> way
> of partitioning and efficiently using identity ranges. With planning you
> should be able to have it parcel out the identity range chunks you need.
> You
> should by now have a handle on what the representative subscriber and
> publisher ranges should be.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> <micks_address@.yahoo.co.uk> wrote in message
> news:1117100177.816930.108770@.z14g2000cwz.googlegr oups.com...
>