sql server data warehouse: Our database is a SQL Server 2000 (sp3). We are
populating the database from transactions on the web which
run through an IPSEC tunnel.

Over the course of several days our counter column on one
of our tables has gaps in the numbering. The gaps stopped
for a few days and then one day started again. This is
all while having a relatively similar volume of traffic.

The table and the counter column are super straight
forward. The table simply receives insert statements. We
aren't duplicating or replicating anything.

The column is the primary key, identity = Yes, identity
increment = 1.

My questions are:

1) How does a new row's counter get incremented in a
situation like this? Could it be that we aren't really
missing records, that the row simply incremented wrong?

2) What might be the possible reason for the counter
number to have gaps occurring?

Any insight would be greatly appreciated.

Thanks,

hi Angelique,
There are chances that you will find gaps in the identity columns.

One of the possibility is, that after executing INSERT statement transaction
is getting rolled back.once transaction is rolled back the whenever next
insert statement will successfully completed it will generate the new
identity value and earlier identity value will be missing in the sequence.

Ex:

create table t(col1 int identity(1,1))
go
insert into t default values
begin transaction
insert into t default values
rollback --transaction rolled back hence identity value 2 is missing
insert into t default values
select * from t


--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

One thing that we have to remember for web based databases - not everytime
does a transaction complete itself. What I mean by that is that there could
be instances wherein the transaction tried to complete itself but got
rolled back because of timeouts, some errors etc.

When a transaction gets rolled back, you will find that those increments of
your identity column will never be used again. Hence, the next time the
transaction takes place, the increment value will begin from a latter
number. And hence those gaps.

You can test it yourself on a database in your machine. Try to insert some
records with the following requisites:

BEGIN TRANSACTION
[your code to insert some records into a table with identity column set]
ROLLBACK TRANSACTION

Once you do this, try to insert records once again without the transaction
rollback option as mentioned above. You should see those gaps again.

Hope that helps.


sanchans@online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.