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

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.

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...