Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
As part of my experiments with the sequence object, I wanted to see what allows me to restart a sequence at a new value. This is useful in a few situations, some of which I want to see in this post.
Starting Over
One common scenario might be where I create a sequence and test it a few times, but don’t want those values lost. For example, Suppose I create this sequence and test it a few times.
CREATE SEQUENCE Counters.TopTen
START WITH 1
MAXVALUE 10
CYCLE
GO
SELECT NEXT VALUE FOR Counters.TopTen
GO
SELECT NEXT VALUE FOR Counters.TopTen
GO
I don’t want the first two values to be removed from the sequence. Instead, I want to get the next number back to 1. I could run 8 more SELECTs to allow the sequence to cycle, but if you’re like me, you’ll end up executing this one too many times and then have to repeat the experience.
Instead, I can use the ALTER command to fix this.
ALTER SEQUENCE counters.TopTen RESTART WITH 1
Of course, I’ll test this with a SELECT, but once I am confident this behaves as expected, I’ll re-run the ALTER again.
Going Backwards
One common situation might be a case where an application requests a number of sequence numbers for a situation, but they never get inserted. Suppose I set up an insert statement to load some data in a table, but a key error or some other problem prevents the inserts. I don’t want those values to be lost, so I want to restart numbering.
As an example, I find that one of my sequences has the value, 41.
However, this is because a load of new products failed. The last number used in the table was 8.
In this case, I want to reset the sequence object to 9, so let’s do that.
ALTER SEQUENCE Counters.Products RESTART WITH 9
Now I can proceed on loading products into this table, using the sequence object to get the next value.
SQLNewBlogger
This was a continuation of a series of posts on the sequence object. As I continued to experiments, I captured the code and some images to use in posts, writing this up as I had time.
For this post, I took about 10 minutes of experimenting and then another 5-10 trying to sort out some of the experiments into an area. This writeup was about 10 more minutes.
