Currently I need to control the generation of IDs (primary key for a table). Thus, I cannot use IDENTITY autoincrement columns. The most stringent requirement is that the IDs should not have gaps since it will be printed on receipts.

I'm sure people have encountered this problem before. Any ideas or comments on how to design this ? Thanks.

Sequence numbers printed on receipts serve a completely different purpose than id numbers used in a database. Confusing the two is an invitation to problems.

Sequence numbers used on printed forms exist to make it possible to prove that the forms were used or accounted for. This is a basic procedure used to prevent clerks from issuing a receipt to a customer and then pocketing the proceeds without filing the receipt with the business. By having the receipt forms pre-numbered, and being able to account for all of the forms, the business owner can ensure that none of the receipt forms are un-accounted for (and therefore that none of the forms could have been mis-used).

Sequence numbers (pre-printed or computer printed) have NOTHING to do with a well managed database. While your software might be willing to record the pre-printed serial number on a form issued to a customer, the whole concept is bad because the computer ought to generate the forms, so the pre-printing becomes meaningless.

What you are trying to do is expressly forbidden as an accounting control for computer printed forms under GAAP. Any competent auditor should issue a negative notation on every audit of a system that relies on computer generated serial numbers that are used this way.

My advice is to see an accountant or an auditor before you spend any time or money to implement a "feature" that they should strongly discourage.

-PatP

Well, I'm no accountant so I'll address the technical issues:

"Gaps" in auto increment columns happen when an update fails, or if a row is deleted. Since the whole point of receipts is that they are permanent, you shouldn't be deleting anything. Update failure happens like this:

1. DBMS sees you're inserting a new row.
2. DBMS increments the auto_increment, and gives you a number.
3. Update fails.
4. DBMS doesn't reset the auto_increment.

One technique would be to use the auto_id column as a tentative receipt number. A scheduled task would periodically lock down the table and issue out permanent receipt numbers, thus ensuring that numbers are only issued to successfully inserted records. Also, in line with Pat's advice, the scheduled task could have a premade list of receipt numbers to draw on.

Thanks Pat for the legal point of view.

sco08y, one way to solve the problems of gaps (in my opinion) is to implement a sort of pushback of IDs.

Another solution would be to simply renumber them to remove any gaps. This can be done with a single update statement, and it is a lot easier than doing the dance needed to support "pushback" to fill the gaps.

-PatP

Pat: yeah, I thought of the pushback and renumbering methods, but in both of them you either occasionally have a gap or have to change IDs. With the method I suggested, if you do "select * from table where permanent_ID is not null" you're guaranteed to see gapless IDs and the IDs will never be renumbered. In addition, if you forced the permanent IDs to be updated at the beginning of your transaction you don't have to use the where clause.

Sorry, I should have used a smiley... The suggestion about renumbering was a joke. I guess that I've worked around the AICPA folks too long, that one seemed obvious to me, but in retrospect it wouldn't be funny to anyone except an accountant.

-PatP

ergo not funny at all

pootle flump ur codings are working excelent.

Pat's comments may well be correct. But there are people out there who have very good reasons for having next-sequential-numbers as part or all of their PK, and are not subject to GAAP. Most respondents here have already disagreed with me re the subject in previous threads, but the original poster has a question which has not been answered.
- do not use IDENTITY feature, it is a dogs breakfast. Especially if you consider the Child tables (where the said PK is an FK), which most of us have. Severely impedes data migration (of the table and its children) which is a requirement once you have prod/test/dev environments.
- do use some sort of method of distributing the key over the table (eg. InvoiceId [PK] is CustomerId plus InvoiceNo; InvoiceNo is simply generated in your app code by (of course, in a transaction):
select max(InvoiceNo) where CustomerId = @CustomerId, plus one
Before any screams, most DB vendors have enhanced the MAX() so that it is no longer a problem. Of course, you have to use optimistic locking, and do this as the last (NOT first) item in the tran.
- if you cannot get the user to agree, then the other alternative is a carefully designed (physical features to ensure small locking duration) Control table which contains a row which hold the LastInvoiceIdUsed. Ugly but not uncommon.
- no gaps. period. Unless your transaction design is out to lunch.
- I do not agree with background or after-the-fact changes because the kind of people who want next-sequential-no want one that does not change
There are other High performance methods but they are not for publication.

Cheers

Again...
There is absolutely nothing wrong with using Identity values (at least as far as SQL Server goes...I can't speak for all databases, so maybe Sybase sucks with identies, I don't know...).
Identities do not create performance issues.
Identities do not create any more data migration headaches than other non-GUID surrogate keys.
Identity columns are preferable to the old-style "GetMaxID+1" method, which is a coding style that is 10 years out of date. The only time you see the GetMaxID+1 method these days is in legacy systems.

I'm curious Derek, I've seen many folks specify that they wanted sequential numbers, and I've had a couple of cases where the people paying the bills told me that they wanted sequential numbers and that is all that they'd pay for, but I've never seen any logically correct argument for sequence. There are certainly lots of good arguments for uniqueness, but I've never heard one for serialization.

I don't know of any method for generating sequence numbers that works any better than the IDENTITY property. Every method I know of can produce gaps in a multi-user, transactional system because those gaps are actually part of the definition of the problem (because of the multi-user and transactional specifications). Unless you redefine the problem that you're solving, I don't believe that there is any solution for it.

-PatP

IMO, this is just a holdover from the days of paper records. It's a "We have Always Done it This Way" argument. (which I run into all the time at my company - usually, I can get the requestor to see reason. )

I would like to know what happens when you insert rows 1,2,3,4,5,6,7,8,9,10

And then you delete 3 and 5

What do you want to have happen?

Reassign 3 and 5?

What about children tables, do you want everything to cascade delete

And what if you export data to som other system...

3 was once an apple, now it's an orange?

Makes no sense and seems dangerous

To be fair, in an accounting application records should not be deleted anyway. Instead, an offsetting transaction should be created.

So true, and a reason why, and who did it and when