Author: way0utwest

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I haven’t used sequences much in my work, but I ran into a question recently on how they work, so I decided to play with them a bit.

Sequences are an object in SQL Server, much like  a table or function. They have a schema, and are numeric values. In fact, the default is a bigint, which I think is both good, and very interesting. Since this will implicitly cast down to an int or other value, that’s good.

The sequence is created like this:

CREATE SEQUENCE dbo.SingleIncrement
  AS INT
  START WITH 1
  INCREMENT BY 1;

GO

These can be similar to identity values, and in fact, if I make 5 calls to this object, I’ll get the numbers 1-5 returned. Here I’ve made one call.

This is interesting, as the NEXT VALUE FOR is what accesses the sequence and returns values. I can use this in some interesting ways. For example, if I have to insert values into a table, I can do this:

CREATE TABLE SequenceTest

( SequenceTestKey INT IDENTITY(1,1)

, SequenceValue INT

, SomeChar VARCHAR(10)

)

GO

INSERT dbo.SequenceTest

(
     SequenceValue,
     SomeChar

)

VALUES
   (NEXT VALUE FOR dbo.SingleIncrement, 'AAAA')

, (NEXT VALUE FOR dbo.SingleIncrement, 'BBBB')

, (NEXT VALUE FOR dbo.SingleIncrement, 'CCCC')

, (NEXT VALUE FOR dbo.SingleIncrement, 'DDDD')

, (NEXT VALUE FOR dbo.SingleIncrement, 'EEEE')

When I query the table, I see:

Notice that the sequence number is off by one from the identity. This because I first accessed the sequence above.

The sequence is independent of a table or columns, unlike the identity. this means, I can keep the sequence numbers going between tables. For example, let’s create another table.

CREATE TABLE dbo.NewSequenceTest

( NewSequenceKey INT IDENTITY(1,1)

, SequenceValue INT

, SomeChar VARCHAR(10)

)

GO

Now, we can run some inserts to both tables and see what we get.

INSERT dbo.NewSequenceTest VALUES (NEXT VALUE FOR dbo.SingleIncrement, 'FFFF')

INSERT dbo.SequenceTest    VALUES  (NEXT VALUE FOR dbo.SingleIncrement, 'GGGG')

INSERT dbo.NewSequenceTest VALUES (NEXT VALUE FOR dbo.SingleIncrement, 'HHHH')

INSERT dbo.SequenceTest    VALUES  (NEXT VALUE FOR dbo.SingleIncrement, 'IIII')

INSERT dbo.NewSequenceTest VALUES (NEXT VALUE FOR dbo.SingleIncrement, 'JJJJ')

After running the inserts, I’ll look at both tables. Notice that the values for the sequence are interleaved between the tables. The first insert to the new table has the value, 7, which is the next value for the sequence after running the inserts for the first table.

In these tests, I’ve used 11 values so far. I can continue to use values, not just for inserts, but elsewhere.

This behavior is both fun, handy, and useful, but also dangerous. These values get used when I query them, whether the inserts work or not. Here’s a short test to look at this:

ALTER TABLE dbo.SequenceTest ADD CONSTRAINT SequencePK PRIMARY KEY (SequenceTestKey)

SELECT NEXT VALUE FOR SingleIncrement

SET IDENTITY_INSERT dbo.SequenceTest ON

INSERT dbo.SequenceTest VALUES (NEXT VALUE FOR SingleIncrement, 'ZZZZ')

SET IDENTITY_INSERT dbo.SequenceTest OFF

SELECT NEXT VALUE FOR SingleIncrement

This gives me an error:

and I can see the last SELECT has the next sequence value.

There are a lot more to sequences, but I’ve gone on long enough here. This is a good set of basics to experiment further, which I’ll do in future posts.

SQLNewBlogger

This post went on longer than expected, and it was more of a 15-20 minute writeup as I set up a couple quick examples, tore them down, and rebuilt them with screenshots for the post.

This is a place where I can show I’ve started to learn more, and by continuing with other items in this series, I’ll show some regular learning.