Crazy Identity Numbers

Joe Hanish

unread,

Jun 26, 1997, 3:00:00 AM6/26/97

to

I'd really appreciate any help anyone can give me on this, or just a reason
why?

I'm trying to use a numeric identity field, and I'm having no luck. It
works fine
initially (increments by one each time, for each record I add) but once the
server
gets shut down and brought back up, the next record that is added has some
CRAZY number like 5000000000000000003 or something, no exaggeration. Can
anyone
tell me how to stop this, or should I just create an integer field and
increment it
myself. I've heard of problems with that also though... Please help me!
I'm using
Sybase SQL Server 11 for Windows NT.

Joe Hanish
j...@leanbody.com

Jibu Abraham

unread,

Jun 27, 1997, 3:00:00 AM6/27/97

to Joe Hanish

Joe Hanish wrote:
>
> I'd really appreciate any help anyone can give me on this, or
> just a reason why?
>
> I'm trying to use a numeric identity field, and I'm having no luck.
> It works fine initially (increments by one each time, for each
> record I add) but once the server gets shut down and brought back up,
> the next record that is added has some CRAZY number like
> 5000000000000000003 or something, no exaggeration. Can anyone
> tell me how to stop this,

To my knowledge, you can't stop this from happening. Once the server
is cycled, a new set of blocks containing linearly sequential numbers
is brought into the cache. When an insert is made to a table containing
the identity column, the server uses the first number chosen from one
of these blocks in the cache. Once all the numbers in the block are
used, the server will move to another block of numbers. I don't know
of any way to *make* the server guarantee that the blocks chosen are in
sequential order. The purpose of the identity property is to uniquely
identify each row in a table that has the identity column. There is
no guarantee that the data values in the identity column will be in
sequential order.

> or should I just create an integer field and increment it
> myself. I've heard of problems with that also though... Please
> help me!
> I'm using Sybase SQL Server 11 for Windows NT.

If you're trying to make the data value unique and sequential, then
it may be better to create an integer field and increment yourself.

Best wishes,
Jibu
--
----------------------------------------------------------------------
Jibu Abraham Database Engineer/Software Developer
Millennium e-mail: jibu_a...@hotmail.com
One Kendall Square, Bldg 300 phone: (617)679-6818
Cambridge, MA 02139 fax: (617)225-2997
----- Disclaimer: Opinions expressed here belong to my cat Fefe! -----

Tom McCready

unread,

Jun 27, 1997, 3:00:00 AM6/27/97

to

"Joe Hanish" <j...@leanbody.com> wrote:

>I'm trying to use a numeric identity field, and I'm having no luck. once the

>server
>gets shut down and brought back up, the next record that is added has some
>CRAZY number like 5000000000000000003 or something, no exaggeration. Can
>anyone

>tell me how to stop this, or should I just create an integer field and

>increment it
>myself. I've heard of problems with that also though... Please help me!
>I'm using
>Sybase SQL Server 11 for Windows NT.

Really more of a question, Joe. I have heard that under High
Availability in Unix, when one cpu goes down and another takes
over, identity numbers become duplicated -- I saw this in an
posting in the Oracle newsforum. But you are running NT and
not Unix and not High Availability, and Sybase not Oracle. I
am not slamming Oracle here, but rather pondering how like the
two situations are.

What problems have you heard about with a numeric field which
you control (as opposed to an identity column which Sybase
controls)? If you lock the page which has that field while you
increment it, what problems could you have? There will be gaps
in sequences. These gaps are caused by rollbacks. A gap of the
size you mentioned, from an early value, to a
value of 500000000003, would disturb me, too. But the Sybase
identity columns are not integers, are they? aren't they some
internal representation, possibly hexadecimal?
Tom

Jon

unread,

Jun 27, 1997, 3:00:00 AM6/27/97

to

Behavior of identity columns and how the next value is determined is
documented in Volume 1 of the SQL Server Reference Manual. It relates
to the identity burning set factor. The documentation is not all that
clear, but my understanding of it is:

The default value of the identity burning set factor is 5000. What
this means is that when the server crashes or is stopped via a
shutdown with nowait, the next identity value will depend on the size
of the identity column.

The default of 5000 specifies that .05 percent of "potential" column
values are contained in each block of available numbers. After a
crash or unclean shutdown, identity values will begin at the next
block, ergo an identity column defined such that at max it could
contain 1000000000 as the largest value would allocate identity values
in blocks of 50000000, giving you gaps as large as 49,999,999.

1000000000 * .05 = 50,000,000

As it states in the manual, to change the value of these blocks you'd
determine the size of the largest gap you want as a percentage of
possible values, multiply it by 10,000,000 (10^7) and supply this
value to sp_configure "identity burning set factor", value.
So, if you wanted a maximum of .001 percent of your possible values
as the largest gap, you'd issue a
sp_configure "identity burning set factor",100

(.00001 * 10,000,000 = 100)

In the above example, if the largest value that could be held in your
identity column is 1000000000, then the blocks of numbers are
allocated 10,000 at a time, so the largest gap you should get is 9999.

Jon
-----------------------------------------
"Life is a tragedy for those who feel,
And a comedy for those who think..."

j...@tiac.net
-----------------------------------------

David Lance Wolf

unread,

Jun 27, 1997, 3:00:00 AM6/27/97

to

Joe Hanish wrote:
>
> I'd really appreciate any help anyone can give me on this, or just a reason
> why?
>

> I'm trying to use a numeric identity field, and I'm having no luck. It
> works fine
> initially (increments by one each time, for each record I add) but once the

> server
> gets shut down and brought back up, the next record that is added has some
> CRAZY number like 5000000000000000003 or something, no exaggeration. Can
> anyone
> tell me how to stop this, or should I just create an integer field and
> increment it
> myself. I've heard of problems with that also though... Please help me!
> I'm using
> Sybase SQL Server 11 for Windows NT.
>

> Joe Hanish
> j...@leanbody.com

Joe-

You need to check the 'identity burning factor' that is set in the
sp_configure/config file.

Dave Wolf
Sybase Professional Services.

This is pasted from the SQL Server Systems Administrators Manual

IDENTITY columns are columns of type numeric and scale zero whose value
is generated by SQL Server. Column values can range from a low of 1 to a
high determined by the column precision.

For each table with an IDENTITY column, SQL Server divides the set of
possible column values into blocks of consecutive numbers, and makes one
block at
a time available in memory. Each time you insert a row into a table, SQL
Server automatically assigns the IDENTITY column the next available
value from the
block. When all the numbers in a block have been used, the next block
becomes available.

This method of choosing IDENTITY column values improves server
performance. When SQL Server assigns a new column value, it reads the
current
maximum value from memory and adds 1. Disk access becomes necessary only
after all values within the block have been used. Because all remaining
numbers in a block are discarded in the event of server failure (or
shutdown with nowait ), this method can lead to gaps in IDENTITY column
values.

Use identity burning set factor to change the percentage of potential
column values that is made available in each block. This number should
be high enough
for good performance, but not so high that gaps in column values are
unacceptably large. The default value, 5000, releases .05 percent of the
potential
IDENTITY column values for use at a time.

To get the correct value for sp_configure , express the percentage in
decimal form, and then multiply it by 10 (^ 7 ) (10,000,000). For
example, to release
15 percent (.15) of the potential IDENTITY column values at a time, you
specify a value of .15 times 10 (^ 7 ) (or 1,500,000) in sp_configure :

sp_configure "identity burning set factor", 1500000

Jon

unread,

Jun 27, 1997, 3:00:00 AM6/27/97

to

To elaborate a little further, I've done some testing and have
discovered that given a default burning set factor of 5000 and
an identity column defined as numeric(10,0), if you crash your
server or issue a shutdown with nowait you're next identity
value will be lastvalue + about 5,000,000, leaving you with
gaps in the 5,000,000 range every time you issue a shutdown
with nowait or the server crashes. If you change the burning
set factor to 100, your gaps will shrink down to 100,000 and
if you change the burning set factor to 10, your gaps will go
down further still, to 10,000.

So the formula would be...

Identity Column Size to the 10th power,
(numeric(10,0) = 10 to the 10th = 10,000,000,000)

Take this number and multiply it by the
burning set factor divided by 1,000,000
(5000 / 1,000,000 = .0005)

This will give you your gap size
(10,000,000,000 * .0005 = 5,000,000)

It seems to work for all the burning set factors I tested
using a numeric(10,0) identity column, as follows:
(10,000,000,000 * (5000 / 1,000,000) = 5,000,000)
(10,000,000,000 * (100 / 1,000,000) = 100,000)
(10,000,000,000 * (10 / 1,000,000) = 10,000)

Here's the testing I did to come up with these numbers.

Default identity burning set factor of 5000.

Using table identtest, defined as:
create table identtest(cola numeric(10,0), colb char(10))

I did the following:

insert into identtest values ("one")
insert into identtest values ("two")
insert into identtest values ("three")
insert into identtest values ("four")
insert into identtest values ("five")
insert into identtest values ("six")
insert into identtest values ("seven")
insert into identtest values ("eight")
insert into identtest values ("nine")
insert into identtest values ("ten")
insert into identtest values ("eleven")
select * from identtest
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
11 eleven