auto-number/Identity column

I am migrating a web application I wrote from ASP to ASP.Net, and from
Access to MS SQL server.

In the Access version, I did not use the auto number for creating
invoices and other documents, because I heard somewhere (perhaps
incorrectly) that if the db was ever compacted or otherwise changed,
it could change the values of the auto-numbers. Not a good thing.
So I wrote a routine that, just before creating a new record, would
look for the highest value in the table and create the new record with
the next number.

So my question is, am I safe in assuming that in MS SQL that I can set
a starting number for the next, let's say, invoice and that new
numbers will be issued in sequence, and that these numbers will never
change? What happens if an invoice is deleted? is the number gone
forever? Just wondering how others deal with these issues...thanks.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."Larry Rekow (larry@.netgeexdotcom) writes:
> So my question is, am I safe in assuming that in MS SQL that I can set
> a starting number for the next, let's say, invoice and that new
> numbers will be issued in sequence, and that these numbers will never
> change? What happens if an invoice is deleted? is the number gone
> forever? Just wondering how others deal with these issues...thanks.

If you need sequential numbers, and cannot accept gaps, you should not
use the IDENITY property. If you attempt to insert a row, and the
insert fails, that consumes a number. The whole point is that the number
is not transactional, so that it scales better. If you need a contiguous
series of numbers, roll your own.

What does not happen is that once the number has been given to a row,
the number will not change at whim.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 31 Aug 2004 21:06:39 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Larry Rekow (larry@.netgeexdotcom) writes:
>> So my question is, am I safe in assuming that in MS SQL that I can set
>> a starting number for the next, let's say, invoice and that new
>> numbers will be issued in sequence, and that these numbers will never
>> change? What happens if an invoice is deleted? is the number gone
>> forever? Just wondering how others deal with these issues...thanks.
>If you need sequential numbers, and cannot accept gaps, you should not
>use the IDENITY property. If you attempt to insert a row, and the
>insert fails, that consumes a number. The whole point is that the number
>is not transactional, so that it scales better. If you need a contiguous
>series of numbers, roll your own.
>What does not happen is that once the number has been given to a row,
>the number will not change at whim.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
thanks; glad I asked.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."