I wrote about sequences in an editorial recently and decided to start using them. Creating one turned out to be surprisingly easy.

I had a table I’d been logging some data in, with a PK, but no natural key or identity bound to the table. Instead, this was low volume (1x per day) and I just manually adjusted the PK value.

I decided to create a sequence. As I did this, I found it interesting and consulted the BOL page for some ideas on the options.

The first part is simple. A name and datatype.

CREATE SEQUENCE dbo.MyKey as INT

That makes sense. Next, I need a starting value. In my case, I had 7 values in the table, so I added this:

START WITH 8

That gets me what I need. The last part I added was the INCREMENT BY clause. In my case, 1 works fine. My code:

The last query is how you get the next value. The NEXT VALUE FOR phrase can be used in various places, but that’s for another day.

If I needed to bind values, I could use the MINVALUE or MAXVALUE, which I’ll look at in a different post. I could also control caching and cycle values if I needed to.

SQLNewBlogger

I actually started a post on binding this to a column and then decided to add this short post in about 5 minutes.