Sql server leave gaps between the id's - how to prevent this?

[21KristianN]

Dear everybody

I have a question!

I have a database with a invoice table for storing invoice data. In this table i use the InvoiceID as my invoice number.

My problem is that if a user makes a mistake and the invoice is deleted in order to create a new one the sql server 2005 picks a new id number and leaves a "gap" between the id numbers.

The invoice id is my identity columne and the data type is "int". How do I set the server to fill evetually gaps before moving forward in the id numbers..

I really hope somebody can help me...

[debasisdas]

Find out the max value of the field and add 1 to generate the new value.

[21KristianN]

I don't Think i quit understand what you are trying to teach me. Ill explain my problem bettet.
I am using sql server 2005 and i need help to find the place where i configure my tabel id " invoiceID" to use all id's instead of skipping previously assigned id's. Example: If i create a new record in my table this record Will be assigned with id 10001 and if i then delete this record the next record should again be assigned with id 10001 because this id i available and not 10002 which is the next id.
I hope this is more clear..

[sknake]

This is a bad idea. You should not create the record if you intend to delete or have a "quotes" table for storing data before it becomes a live invoice. If you create invoice #5 and then add a payment to invoice #5, then delete invoice #5 and recreate it, then your payment from the old invoice would automatically be associated to the new one. To answer your question you can reseed the table: DBCC CHECKIDENT('mytable', RESEED, 0) ; or enable IDENTITY_INSERT . This functionality requires dbo rights so it may be a security concern.

[debasisdas]

That is not the way to work with databases. If you want to avoid gaps then just follow post #2. But that is not going to fulfill your requirement of deleted records.

[21KristianN]

Thank you for your quick repiles and perspectives. I see the problems with my way of doing this but i really dont know how to solve it then.

I have a Invoice table with all my invoice data.. The user can with admin rights acces this table and delete evetually wrong created invoices. My problem is that the national vat and tax department requires that invoices are created in a unbroken numeric pattern. This causes me a huge problem because if a user create a totally wrong invoice and then delete it then the numeric pattern i broken. So i really don't know how to solve it. Perhaps I should insert a "deleted" field on each invoice and then leave thise records out in the display for the users.. Any good ideas?? how would u guys have done it..

[sknake]

The only way I know to enforce this would be to only permit single records inserts. If you you use an Insert Into .. Select From .. it would break the sequencing logic. You could also have a stored procedure that you fire off before you print invoices that updates the database with any null values to the current number in the sequence. Also what happens if you have invoice #1,2,3 .. then #2 gets deleted but you don't have another invoice by the end of the month so you only have #1,3? Would you have to change #3 to #2? Can you just generate these invoice numbers when you're doing your tax reports and use a different invoice just for this purpose? I have tried a number of different approaches and they all have issues.

Here is the only solid logic I have regarding your design to stop an invoice number from being changed in an update statement:

IF OBJECT_ID('dbo.InvoiceTbl', 'U') IS NOT NULL DROP TABLE InvoiceTbl

GO

Create Table InvoiceTbl
(
  PK int identity(1000, 1) PRIMARY KEY,
  InvNumber int UNIQUE,
  CustName varchar(50)
)

GO
IF OBJECT_ID('tr_InvoiceTbl_Update', 'TR') IS NOT NULL DROP TRIGGER tr_InvoiceTbl_Update
GO
CREATE TRIGGER tr_InvoiceTbl_Update ON InvoiceTbl WITH EXECUTE AS CALLER FOR UPDATE AS
IF UPDATE(InvNumber)
BEGIN
  --Stops changing of the invoice number after the record has been created
  IF EXISTS
  (
    Select * 
    From Deleted FULL Join Inserted On (IsNull(Deleted.PK, -1) = IsNull(Inserted.PK, -2)) 
    Where IsNull(Deleted.InvNumber, -1) <> IsNull(Inserted.InvNumber, -2)
  )
  BEGIN
    RAISERROR ('cannot change invoice number', 16, 1)
    ROLLBACK TRAN
    RETURN
  END  
END

However if you need to run an update after the fact to set an invoice number that trigger will cause the update to roll back.

[mail2saion]

Yes you can't achieve this by using identity column. Better try to generate Id from application end.

[pclfw]

It would probably be better, especially for audit trails etc, to leave the current IDENTITY column intact and add another column to show if the row is active or deleted.

Then your users can make their current allocation of errors and just tag the record as deleted. The VAT / TAX man will be happy because your invoice table won't have gaps in it. And your accountants / auditors will also be happy that they can see all of the errors your data entry people make.

[debasisdas]

Try to use a procedure to insert the records into the database.

Try this sample oracle code.(convert to sql server code as per your requirement)

create or replace procedure find(eno number, flag out boolean)
is
a number(2);
begin
select count(*) into a from emp where empno=eno;
if a=0 then
flag:=FALSE;
else
flag:=TRUE;
end if;
end;
/


create or replace procedure ins_emp(a number)
is
eno emp.empno%type;
fg boolean;
begin
eno:=a;
<<abc>>
find(eno,fg);
if fg=true then
dbms_output.put_line(eno||'  No already exists...');
eno := eno+1;
goto abc;
else
insert into emp(empno) values(eno);
dbms_output.put_line('New empno inserted is : '||eno);
end if;
end;
/

[pclfw]

None of these 'solutions' get over the fact that you are deleting records from an invoice table. In the UK this would be considered unauditable and you would be unable to get these deletions past SOX or JSOX compliance.

Deleting records in your accounting trail is, at least unwise; certainally stupid and probably a criminal act.

[21KristianN]

Hi again everybody.. Just arrived home from skiing holidays. Thank you for All your suggestions. I have solved it with All your recommendations in mind. I'll clearly go for an application generated invoice number and leave the identity number as unique. Furthermore, i have investegated the audit rules regarding deletion of data and it is actually not legal in my Country as well. So i'll create a field for active/deleted status on all my records and just sort leave out deleted records in client interface. Thank you all for helping mé and providing me with a greater insight in the world of deleting audit data.