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.