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 Problem

2 posts in Windows NT Last posting was on 1999-12-29 23:46:16.0Z
Esteban Caldentey Morales Posted on 1999-12-29 23:46:16.0Z
Message-ID: <386A9D47.9A8E2AB1@chilesat.net>
Date: Wed, 29 Dec 1999 19:46:16 -0400
From: Esteban Caldentey Morales <ecaldent@chilesat.net>
X-Mailer: Mozilla 4.03 [es] (WinNT; I)
MIME-Version: 1.0
Subject: Identity Problem
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 19
NNTP-Posting-Host: 200.28.212.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2761
Article PK: 1090198

Hi there, i have already ended the migration of a database, from MSSQL70
to ASE 11.9.2. The database has some tables that use an IDENTITY field
as the primary key. Everything looked okey until i inserted the first
record into a table where the IDENTITY field is defined as a
numeric(12,0), the first value that the database generated, was
500000001, i don't know why. I thought i did a wrong creation of the
table so i have recreated it. I repeated the insertion, and now the
first value was 1, but after a while the database started generating
values over 500000000, example 1,2,3 ..9,10,10,500000001,500000002.

Does anybody knows what must be wrong? Any help will be appreciated.

Thank in advance Esteban.


Bret Halford Posted on 1999-12-29 23:25:15.0Z
Message-ID: <386A985B.7DD@sybase.com>
Date: Wed, 29 Dec 1999 16:25:15 -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: Esteban Caldentey Morales <ecaldent@chilesat.net>
Subject: Re: Identity Problem
References: <386A9D47.9A8E2AB1@chilesat.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 42
NNTP-Posting-Host: 157.133.214.40
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2760
Article PK: 1090200


Esteban Caldentey Morales wrote:
>
> Hi there, i have already ended the migration of a database, from MSSQL70
> to ASE 11.9.2. The database has some tables that use an IDENTITY field
> as the primary key. Everything looked okey until i inserted the first
> record into a table where the IDENTITY field is defined as a
> numeric(12,0), the first value that the database generated, was
> 500000001, i don't know why. I thought i did a wrong creation of the
> table so i have recreated it. I repeated the insertion, and now the
> first value was 1, but after a while the database started generating
> values over 500000000, example 1,2,3 ..9,10,10,500000001,500000002.
>
> Does anybody knows what must be wrong? Any help will be appreciated.
>

Hi Esteban,

There isn't necessarily anything wrong at all. Was the server shut down
abruptly (with NOWAIT, for instance, or by powering down) between the
creation of the row with value "10" and the row with value "500000001"?
(I'm assuming the duplicate 10 is a typo here). The identity feature is
designed to generate gaps like this when the server is abruptly shut
down. The relative size of the gap is controlled by the sp_configure
"identity burning set factor" setting, which represents a percentage of
the total range of values.

Have a look at http://www.euronet.nl/~syp_rob/idgaps.html for further
discussion and an architectural workaround if you don't want the gaps.

Note that 12.0 has a new feature that lets you set the absolute size of
the gap on a table by table basis.

--
Bret Halford
Sybase Technical Support
3665 Discovery Drive
Boulder, CO 80303