SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Restarting a Sequence–#SQLNewBlogger

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.

2018-12-05 15_49_08-sequences.sql - dkrSpectre_SQL2017.sandbox (DKRSPECTRE_way0u (65))_ - Microsoft

However, this is because a load of new products failed. The last number used in the table was 8.

2018-12-05 15_49_29-sequences.sql - dkrSpectre_SQL2017.sandbox (DKRSPECTRE_way0u (65))_ - Microsoft

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...