Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Identity field jumping values

6 posts in Windows NT Last posting was on 1999-10-26 09:00:58.0Z
Adam Posted on 1998-02-10 13:33:09.0Z
From: "Adam" <adam@foxvalley.net>
Subject: Identity field jumping values
Date: Tue, 10 Feb 1998 07:33:09 -0600
Lines: 18
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <R76SO9hN9GA.128@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5217
Article PK: 1081296

What causes an identity field to skip values?

I just finished putting up a web site and have a "hit counter" type table.
The table has 2 fields, an identity counter and a date field. Everything was
fine until I moved the computer and set it up and checked it out on the
web...

My hit counter field jumped from 30 to 50015 and also an order table jumped
from 13 to 3000+.

This isn't the first time it has happened and I need to fix it. Is there
anyway to reset the number and how do I keep it from happening?

Thanks

adam@foxvalley.net


William Green Posted on 1998-02-10 17:52:07.0Z
Message-ID: <34E093C7.72B8@sybase.com>
Date: Tue, 10 Feb 1998 09:52:07 -0800
From: William Green <wgreen@sybase.com>
Reply-To: wgreen@sybase.com
Organization: Telesales SC
X-Mailer: Mozilla 3.0 (Win16; I)
MIME-Version: 1.0
To: Adam <adam@foxvalley.net>
Subject: Re: Identity field jumping values
References: <R76SO9hN9GA.128@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 39
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5216
Article PK: 1081295

Greetings, Adam.

Any chance you've killed the server process? Had a system crash? Been
doing a shutdown with nowait? These could cause gaps...

To find out more, go to:

http://techinfo.sybase.com

and enter "identity" in the search field... and hit enter.

The first document in the result set explains the Identity Burning Set
Factor and Gaps, etc. You might also get onto the latest EBF if you're
not using it already... there *were* some issues a while ago, but they
should be cleaned up by now.

Best of success!

=============================================================
William Green | * | wgreen@sybase.com
Systems Consultant | * | x246-1705
Sybase, Inc. | * | 1-800-8-SYBASE
Concord, MA 01742 | * | CSP/DBA/Team Sybase
=============================================================

Adam wrote:
>
> What causes an identity field to skip values?
>
> I just finished putting up a web site and have a "hit counter" type table.
> The table has 2 fields, an identity counter and a date field. Everything was
> fine until I moved the computer and set it up and checked it out on the
> web...
>
> My hit counter field jumped from 30 to 50015 and also an order table jumped
> from 13 to 3000+.
>
> This isn't the first time it has happened and I need to fix it. Is there
> anyway to reset the number and how do I keep it from happening?
>
> Thanks
>
> adam@foxvalley.net


Bret Halford Posted on 1998-02-10 17:12:35.0Z
Message-ID: <34E08A83.1582@sybase.com>
Date: Tue, 10 Feb 1998 10:12:35 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: adam@foxvalley.ent
Subject: Re: Identity field jumping values
References: <R76SO9hN9GA.128@forums.powersoft.com> <34E093C7.72B8@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 45
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5214
Article PK: 1081293

As William writes, plus:


Identity really is not appropriate for use as a counter. It is
guaranteed
to be unique and sequential, but not gapless.

For a counter, you should use some other technique for generating the
numbers. There is a paper at http://techinfo.sybase.com that explains
a number of techniques for generating such keys. Doc ID is 860.


-bret

William Green wrote:
>
> Greetings, Adam.
>
> Any chance you've killed the server process? Had a system crash? Been
> doing a shutdown with nowait? These could cause gaps...
>
> To find out more, go to:
>
> http://techinfo.sybase.com
>
> and enter "identity" in the search field... and hit enter.
>
> The first document in the result set explains the Identity Burning Set
> Factor and Gaps, etc. You might also get onto the latest EBF if you're
> not using it already... there *were* some issues a while ago, but they
> should be cleaned up by now.
>

> Adam wrote:
> >
> > What causes an identity field to skip values?
> >
> > I just finished putting up a web site and have a "hit counter" type table.
> > The table has 2 fields, an identity counter and a date field. Everything was
> > fine until I moved the computer and set it up and checked it out on the
> > web...
> >
> > My hit counter field jumped from 30 to 50015 and also an order table jumped
> > from 13 to 3000+.
> >
> > This isn't the first time it has happened and I need to fix it. Is there
> > anyway to reset the number and how do I keep it from happening?
> >
> > Thanks
> >
> > adam@foxvalley.net

--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


James Smith Posted on 1999-10-24 15:08:07.0Z
Message-ID: <381320D7.62058E2E@eunite.co.uk>
Date: Sun, 24 Oct 1999 16:08:07 +0100
From: James Smith <jamessmith@eunite.co.uk>
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Identity field jumping values
References: <R76SO9hN9GA.128@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 19
Path: forums-1-dub!forums-master.sybase.com!forums-2.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:3118
Article PK: 1090558

Adam

I am getting a similar problem with Identity fields. I was wondering if you
have found a solution.

Cheers

James

Adam wrote:

> What causes an identity field to skip values?
>
> I just finished putting up a web site and have a "hit counter" type table.
> The table has 2 fields, an identity counter and a date field. Everything was
> fine until I moved the computer and set it up and checked it out on the
> web...
>
> My hit counter field jumped from 30 to 50015 and also an order table jumped
> from 13 to 3000+.
>
> This isn't the first time it has happened and I need to fix it. Is there
> anyway to reset the number and how do I keep it from happening?
>
> Thanks
>
> adam@foxvalley.net


Rob Verschoor Posted on 1999-10-26 09:00:58.0Z
From: "Rob Verschoor" <rob@sypron.nl>
References: <R76SO9hN9GA.128@forums.powersoft.com> <381320D7.62058E2E@eunite.co.uk>
Subject: Re: Identity field jumping values
Date: Tue, 26 Oct 1999 11:00:58 +0200
Lines: 54
Organization: Sypron B.V.
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Message-ID: <ZO#DiB5H$GA.110@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums-2.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:3099
Article PK: 1090538

Check out this document for a discussion of background & solutions:
http://www.euronet.nl/~syp_rob/idgaps.html

HTH,

Rob

--
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase ASE 11.5 Administrator (Professional)
Certified DBA/Performance & Tuning Specialist for Sybase System 11

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW http://www.euronet.nl/~syp_rob
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------

James Smith wrote in message <381320D7.62058E2E@eunite.co.uk>...
>Adam
>
>I am getting a similar problem with Identity fields. I was wondering if
you
>have found a solution.
>
>Cheers
>
>James
>
>Adam wrote:
>
>> What causes an identity field to skip values?
>>
>> I just finished putting up a web site and have a "hit counter" type
table.
>> The table has 2 fields, an identity counter and a date field. Everything
was
>> fine until I moved the computer and set it up and checked it out on the
>> web...
>>
>> My hit counter field jumped from 30 to 50015 and also an order table
jumped
>> from 13 to 3000+.
>>
>> This isn't the first time it has happened and I need to fix it. Is there
>> anyway to reset the number and how do I keep it from happening?
>>
>> Thanks
>>
>> adam@foxvalley.net
>


Tony Imbierski Posted on 1999-10-21 16:28:51.0Z
Message-ID: <380F3F43.7B762DDE@sybase.com>
Date: Thu, 21 Oct 1999 17:28:51 +0100
From: Tony Imbierski <tonyi@sybase.com>
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Identity field jumping values
References: <R76SO9hN9GA.128@forums.powersoft.com> <381320D7.62058E2E@eunite.co.uk>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 32
Path: forums-1-dub!forums-master.sybase.com!forums-2.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:3116
Article PK: 1090554

Hi Adam, James,

There has been *lots* of discussion on this topic - look through the newsgroup for
old messages titled "identity" or search on deja news for an explanation why it
happens and what you can do about it.

The bottom line is that identity columns are not really suitable if you absolutely
have to have a contiguous set of numbers with no gaps. You will need to generate
contiguous numbers in your application.

cheers,
tonyi

James Smith wrote:

> Adam
>
> I am getting a similar problem with Identity fields. I was wondering if you
> have found a solution.
>
> Cheers
>
> James
>
> Adam wrote:
>
> > What causes an identity field to skip values?
> >
> > I just finished putting up a web site and have a "hit counter" type table.
> > The table has 2 fields, an identity counter and a date field. Everything was
> > fine until I moved the computer and set it up and checked it out on the
> > web...
> >
> > My hit counter field jumped from 30 to 50015 and also an order table jumped
> > from 13 to 3000+.
> >
> > This isn't the first time it has happened and I need to fix it. Is there
> > anyway to reset the number and how do I keep it from happening?
> >
> > Thanks
> >
> > adam@foxvalley.net