SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Unique Reference Number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Steve2106
Posting Yak Master

United Kingdom
174 Posts

Posted - 02/17/2014 :  12:04:04  Show Profile  Reply with Quote
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,


Edited by - Steve2106 on 02/17/2014 12:07:35

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/17/2014 :  14:07:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 02/17/2014 :  23:30:02  Show Profile  Reply with Quote
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

United Kingdom
174 Posts

Posted - 02/18/2014 :  04:03:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000