By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,572 Members | 2,949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,572 IT Pros & Developers. It's quick & easy.

auto-number/Identity column

P: n/a
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."
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a
On Tue, 31 Aug 2004 21:06:39 +0000 (UTC), Erland Sommarskog
<es****@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."
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.