Identity Block Reserved For Inserts Issue - Are you good?

[Rob]

Hi all,

I have a bit of a complicated question, hope we have an SQL guru out there
that can help us solve this killer problem.

Due to the size of SQL Database we have (largest in the US), we try to
pre-process large data files in IO until we are ready to insert directly into
the database via BCP (quick, no constraints, no mess... well um that's the
problem)

Because we need to get all the linking before we BCP, we find the current
identity value, push the value up past the number of inserts we are going to
push in from BCP. We then take the block of identities and insert them into
the identity column in our files.

Our problem is that sometimes, the identities from the block somehow get
used or were used by a competing process that uses only automated identity
inserts by SQL.

Why? It really sucks...

Here is a simple example:

Row in the file before "reserving" a block of identities:
Identity|projectid|memberno|name
|313|100|Bob
|313|200|Jane
|313|234|Larry

We then run a sproc like this
Begin Tran
Select top 1 @Member_id_start_value = Ident_Current('Member')
from Member with(TabLockx)

Select @newSeed = @Member_id_start_value + @NumRecords + @range_pad

dbcc checkident(Member, reseed, @newSeed)
Commit Tran

Assuming the current Identity was 1000, and the @range_pad was 5 at the time
of the execution of the sproc:
@Member_id_start_value = 1000
@NumRecords would be 3
@range_pad = 5

making the @newSeed = 1007

We would then take the low value (1000) as the first identity value for the
first record and ++ for each row thereafter resulting in something like this:

identity|projectid|memberno|name
1000|313|100|Bob
1001|313|200|Jane
1002|313|234|Larry

This would then get BCP'd up with "Insert Identity Column" switched on.

There is a lag between getting the block and uploading using BCP of anywhere
from 1 minute to 6 hours.

What happens is that we sometimes find records in the table that have been
assigned an identity in our block.

So, if you got an answer can help us, we'd love to hear it.

BTW - please don't tell me that the problem is because there is a lag
between the time we push the identity past our block range and the insert
from BCP. If that was your answer, you're just un-informed.

Thanks BIG time folks! I know you can do it.

Rob

[DeveloperX]

Rob wrote:

Hi all,

I have a bit of a complicated question, hope we have an SQL guru out there
that can help us solve this killer problem.

Due to the size of SQL Database we have (largest in the US), we try to
pre-process large data files in IO until we are ready to insert directly into
the database via BCP (quick, no constraints, no mess... well um that's the
problem)

Because we need to get all the linking before we BCP, we find the current
identity value, push the value up past the number of inserts we are going to
push in from BCP. We then take the block of identities and insert them into
the identity column in our files.

Our problem is that sometimes, the identities from the block somehow get
used or were used by a competing process that uses only automated identity
inserts by SQL.

Why? It really sucks...

Here is a simple example:

Row in the file before "reserving" a block of identities:
Identity|projectid|memberno|name
|313|100|Bob
|313|200|Jane
|313|234|Larry

We then run a sproc like this
Begin Tran
Select top 1 @Member_id_start_value = Ident_Current('Member')
from Member with(TabLockx)

Select @newSeed = @Member_id_start_value + @NumRecords + @range_pad

dbcc checkident(Member, reseed, @newSeed)
Commit Tran

Assuming the current Identity was 1000, and the @range_pad was 5 at the time
of the execution of the sproc:
@Member_id_start_value = 1000
@NumRecords would be 3
@range_pad = 5

making the @newSeed = 1007

We would then take the low value (1000) as the first identity value for the
first record and ++ for each row thereafter resulting in something like this:

identity|projectid|memberno|name
1000|313|100|Bob
1001|313|200|Jane
1002|313|234|Larry

This would then get BCP'd up with "Insert Identity Column" switched on.

There is a lag between getting the block and uploading using BCP of anywhere
from 1 minute to 6 hours.

What happens is that we sometimes find records in the table that have been
assigned an identity in our block.

So, if you got an answer can help us, we'd love to hear it.

BTW - please don't tell me that the problem is because there is a lag
between the time we push the identity past our block range and the insert
from BCP. If that was your answer, you're just un-informed.

Thanks BIG time folks! I know you can do it.

Rob

dbcc checkident (tablename, reseed, 1007) ?

Use lock hints to lock out the competing processes until you're
finished assigning the identities?

Alternatively manage the identity yourself, write a stored proc that
updates the nextID value in a table somewhere with a parameter that
allows you to allocate a number of rows. Use a lock hint to ensure no
other process can read the value before you've updated and force all
applications to use this stored proc. Perhaps do the same with the dbbc
checkident?

Or add a second identity column used soley by your bulk load app, it
doesn't even need to be an identity as you have sole control over it.

Just some ideas off the top of my head.

[Sylvain Lafontaine]

Well, we don't have the timeline of these inserted records (is their
insertions have been started just before you acquire the lock on the table
or sometime after and after, when? (Before, while or after the BCP?) ) , so
it's hard to tell what's happening here.

Probably that you will hear a lot of comments about the use of
Ident_Current() and dbcc, maybe someone will even suggest you to use the
greatest value in the table Member instead of using Ident_Current();
however, are you sure that it's not the values to be inserted with BCC
themselves that could be wrong?

Finally, I don't understand why you aren't posting this in the
m.p.sqlserver.programming newsgroup.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Rob" <Ro*@discussions.microsoft.comwrote in message
news:7C**********************************@microsof t.com...

Hi all,

I have a bit of a complicated question, hope we have an SQL guru out there
that can help us solve this killer problem.

Due to the size of SQL Database we have (largest in the US), we try to
pre-process large data files in IO until we are ready to insert directly
into
the database via BCP (quick, no constraints, no mess... well um that's the
problem)

Because we need to get all the linking before we BCP, we find the current
identity value, push the value up past the number of inserts we are going
to
push in from BCP. We then take the block of identities and insert them
into
the identity column in our files.

Our problem is that sometimes, the identities from the block somehow get
used or were used by a competing process that uses only automated identity
inserts by SQL.

Why? It really sucks...

Here is a simple example:

Row in the file before "reserving" a block of identities:
Identity|projectid|memberno|name
|313|100|Bob
|313|200|Jane
|313|234|Larry

We then run a sproc like this
Begin Tran
Select top 1 @Member_id_start_value = Ident_Current('Member')
from Member with(TabLockx)

Select @newSeed = @Member_id_start_value + @NumRecords + @range_pad

dbcc checkident(Member, reseed, @newSeed)
Commit Tran

Assuming the current Identity was 1000, and the @range_pad was 5 at the
time
of the execution of the sproc:
@Member_id_start_value = 1000
@NumRecords would be 3
@range_pad = 5

making the @newSeed = 1007

We would then take the low value (1000) as the first identity value for
the
first record and ++ for each row thereafter resulting in something like
this:

identity|projectid|memberno|name
1000|313|100|Bob
1001|313|200|Jane
1002|313|234|Larry

This would then get BCP'd up with "Insert Identity Column" switched on.

There is a lag between getting the block and uploading using BCP of
anywhere
from 1 minute to 6 hours.

What happens is that we sometimes find records in the table that have been
assigned an identity in our block.

So, if you got an answer can help us, we'd love to hear it.

BTW - please don't tell me that the problem is because there is a lag
between the time we push the identity past our block range and the insert
from BCP. If that was your answer, you're just un-informed.

Thanks BIG time folks! I know you can do it.

Rob

[Rob]

This post was submitted to the SQL SERVER thread and will no longer be
monitored by me. Sorry for the inconvenience.

Rob

"Rob" wrote:

Hi all,

I have a bit of a complicated question, hope we have an SQL guru out there
that can help us solve this killer problem.

Due to the size of SQL Database we have (largest in the US), we try to
pre-process large data files in IO until we are ready to insert directly into
the database via BCP (quick, no constraints, no mess... well um that's the
problem)

Because we need to get all the linking before we BCP, we find the current
identity value, push the value up past the number of inserts we are going to
push in from BCP. We then take the block of identities and insert them into
the identity column in our files.

Our problem is that sometimes, the identities from the block somehow get
used or were used by a competing process that uses only automated identity
inserts by SQL.

Why? It really sucks...

Here is a simple example:

Row in the file before "reserving" a block of identities:
Identity|projectid|memberno|name
|313|100|Bob
|313|200|Jane
|313|234|Larry

We then run a sproc like this
Begin Tran
Select top 1 @Member_id_start_value = Ident_Current('Member')
from Member with(TabLockx)

Select @newSeed = @Member_id_start_value + @NumRecords + @range_pad

dbcc checkident(Member, reseed, @newSeed)
Commit Tran

Assuming the current Identity was 1000, and the @range_pad was 5 at the time
of the execution of the sproc:
@Member_id_start_value = 1000
@NumRecords would be 3
@range_pad = 5

making the @newSeed = 1007

We would then take the low value (1000) as the first identity value for the
first record and ++ for each row thereafter resulting in something like this:

identity|projectid|memberno|name
1000|313|100|Bob
1001|313|200|Jane
1002|313|234|Larry

This would then get BCP'd up with "Insert Identity Column" switched on.

There is a lag between getting the block and uploading using BCP of anywhere
from 1 minute to 6 hours.

What happens is that we sometimes find records in the table that have been
assigned an identity in our block.

So, if you got an answer can help us, we'd love to hear it.

BTW - please don't tell me that the problem is because there is a lag
between the time we push the identity past our block range and the insert
from BCP. If that was your answer, you're just un-informed.

Thanks BIG time folks! I know you can do it.

Rob