Link to home
Start Free TrialLog in
Avatar of dylanone
dylanone

asked on

SQl Server Sequence Function Getting To Increment!

I was in need of a sequence function for SQL Server and this article does the trick:

http://www.winnetmag.com/Article/ArticleID/22442/22442.html

As I implemented this example figure 4 with success:

http://www.winnetmag.com/Files/23/22442/Listing_04.txt

However I need it to increment by one each time it's called currently if I run this function like this:

SELECT id FROM fn_sequence(1,1)

It gives me a start value and end value of one which is what I want however I'd like to increment by one each time it's called - is this possible!
Avatar of jdlambert1
jdlambert1
Flag of United States of America image

The only way it can increment each time is if it stores the value between calls. If you use a global variable, it won't survive a stop/start of SQL Server. You can store the value in a table just for that purpose to overcome that durablity problem. Or, my preference would be to fetch the most recent value from the destination table and increment that for the insert (just to avoid creating a table to hold the variable).
ASKER CERTIFIED SOLUTION
Avatar of BillAn1
BillAn1

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of monosodiumg
monosodiumg

Read the article. I use a table of in  quite regularly. I populate a tabkle of ints with values from 0 up to whatever. Don't understand why they present a  solution hardcoded to be limited to 10K.  There doesn't seem a lot of value to being able to specify the initial value since any user can simply add that value to the sequence of ints starting at 0.

If you need it to persists across session then , as others have said, you need persistent storage so you need a table.
Create a table with and indentity column and insert a record to get a new value from the identity generating mechanism. You can delete any records you create on the same occasion. That will give you a monotonic increasing series. Not guaranteed gap free under operational error though.
Avatar of dylanone

ASKER

Perfect - exactly what I needed!