Creating a Sequence in SQL Server 2012

Steve Jones, 2012-05-01

Sequences are a new object in SQL Server 2012 that generate just what the name implies: a sequence of numbers.

To create a sequence, you use standard DDL with the CREATE SEQUENCE command. This command takes a type, a starting value, a min, max, and a few other parameters. You can read about the meanings of the parameters in Books Online, and learn how to use the parameters.

When I first head about sequences, I thought this was neat, but not terribly useful. The idea of a custom sequence seems more like an edge case. However the more I’ve looked at them, the more it starts to make some sense.

Let’s look at a real world example where we use a sequence since we often use these as a basis for some computer algorithm. Suppose I have a group of kids and I need to assign them to groups. In the real world, we line up the kids and we have them count off to some number. It’s often “1”, “2”, “1”, “2”, but it could be something else. In a youth group a few years ago, we counted off into 5 groups to divide the kids up.

In this case, suppose I have kids and I want to get them into these groups:

sequence1

In order to get that in T-SQL, it can be cumbersome, especially if there isn’t a data point that cycles things, but rather a set counter. Here I can create a simple sequence:

create sequence RoundRobin
 Start with 1
 increment by 1
  minvalue 1
  maxvalue 3
 cycle
;

I have 10 rows in my table, being the list of kids:

create table Kids
( Kid varchar(10)
)
go
insert Kids
 values
  ('Emma')
, ('Tabitha')
, ('Kendall')
, ('Delaney')
, ('Kyle')
, ('Jessica')
, ('Josh')
, ('Kirsten')
, ('Amanda')
, ('Jimmy')
;
go

To integrate the sequence, I do this:

select
  'Group' = next value for dbo.RoundRobin
, Kid
 from Kids
;

That returns the results above.

It’s not necessarily a common solution, but there might be places where you want custom counters, perhaps counting by a certain value. Suppose you want to alternate counters among two different people in a table. An identity doesn’t allow this, but you could set up two sequences, say one for even numbers, and one for odd numbers, and let each person use the appropriate sequence for inserts.

Sequences are simple, but they can be useful in some cases. I’ll write a bit more about a few gotchas I’ve run into with sequences soon.

Filed under: Blog Tagged: syndicated, T-SQL

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads