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.