Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Unique Reference Number

Author  Topic 

Steve2106
Posting Yak Master

183 Posts

Posted - 2014-02-17 : 12:04:04
Hi There,

Your help is needed again.

For each record I add to my database I need to create a reference number 1 more than the last one.
I cannot use the auto numbering of Sql Server as in the past that has jumped from 860 to 892 with no explanation. Then all my records are all over the place.
If I have a table with 2 fields, AutoId & InvoiceNum.
Is there a way for me to ignore the AutoId auto numbering and just look into the table, find the latest InvoiceNum and add 1 to that.

Thanks for the help.

Best Regards,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-17 : 14:07:59
If you use a SEQUENCE object then as you've observed you will get gaps in values usually when sql server service is restarted. However, if you use an IDENTITY column that should not happen. But even with identity columns if you insert and rollback within a transaction or if you simple delete a row there will be gaps in the sequence.

Gaps in numeric ID columns should not provide any problem in well designed database. Do you need to present an unbroken sequence to users for some reason? What is the problem with "records all over the place"? You can always derive a user-seen invoiceNum based on something like: select row_number() over (partition by clientid, order by invoiceDate) as InvoiceNum

Be One with the Optimizer
TG
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2014-02-17 : 23:30:02
I would use an identify... Honestly if having a break in sequence is going to cause you am issue, it likely is not a good database structure to begin with. You can always select all records using a row_number as well .


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

Steve2106
Posting Yak Master

183 Posts

Posted - 2014-02-18 : 04:03:02
Hi There,

Thanks for the replies.

I mainly want to use the number for an invoice number but I need them to be consistent. No Gaps.

Any pointers you can give on this would be appreciated.

Best Regards,

Always Learning
Go to Top of Page
   

- Advertisement -