Basic Sequences–#SQLNewBlogger

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.

2018-12-04 13_19_48-SQLQuery6.sql - dkrSpectre_SQL2017.sandbox (DKRSPECTRE_way0u (55))_ - Microsoft

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:

2018-12-04 13_22_50-SQLQuery6.sql - dkrSpectre_SQL2017.sandbox (DKRSPECTRE_way0u (55))_ - Microsoft

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.

2018-12-04 13_27_14-SQLQuery6.sql - dkrSpectre_SQL2017.sandbox (DKRSPECTRE_way0u (55))_ - Microsoft

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

2018-12-04 13_34_02-SQLQuery6.sql - dkrSpectre_SQL2017.sandbox (DKRSPECTRE_way0u (55))_ - Microsoft

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:

2018-12-04 13_36_52-SQLQuery6.sql - dkrSpectre_SQL2017.sandbox (DKRSPECTRE_way0u (55))_ - Microsoft

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

2018-12-04 13_36_45-SQLQuery6.sql - dkrSpectre_SQL2017.sandbox (DKRSPECTRE_way0u (55))_ - Microsoft

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

2 Responses to Basic Sequences–#SQLNewBlogger

  1. Rod Falanga says:

    WOW! I have never heard of Sequences in SQL Server. Very interesting. Thank you for bringing this to my attention.

  2. way0utwest says:

    Glad you enjoyed it. I have a few more posts coming as I experiment with these.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.