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.
| 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 InvoiceNumBe One with the OptimizerTG |
 |
|
|
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 |
|
|
|
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 |
|
|
|
|
|
|
Unique Reference Number