Denali feature: Sequences

SEQUENCES in SQL Server Denali are an improvement over IDENTITY fields in previous versions of SQL Server.  They can, for example:

  • be used as a single incrementing ID across multiple tables
  • be set to recycle
  • be more easily queried

However, the default settings for the SEQUENCE are rather different to the default values for an IDENTITY, and there are a host of other options available.

IDENTITY – Defaults

By default (ie, with no parameters provided), an IDENTITY field starts at 1 and increments by 1 each time a record is inserted into the table. And your only options are to control that start value and the increment.

SEQUENCES – Defaults

Creating a SEQUENCE, however, is a different proposition. Try running:

CREATE SEQUENCE SequenceDefaults;
SELECT * FROM sys.sequences
WHERE name = 'SequenceDefaults'

(Look! You can query your sequences to see what they’re up to!)

Eww. That’s (a) a bit small, and (b) a lot of information, some of which I can’t show you because of the limitations of this PC. Some of the more useful information is shown here:

As you can see from here, we have the minimum value and maximum value. These are mahoosive numbers – and you can see why, when you look at the system_type_id field, which contains 127. Checking in sys.types, you can see that this is an 8 byte number, aka the aptly-named “bigint”. And, by default, the current value is the lowest number that a bigint can be, or -9,223,372,036,854,775,808 – should be enough for anybody, right?

Number ranges

This caused a train of thought… Most of us are used to using integers starting at one. or zero – but let’s not get into that argument right now. These numbers are easy to read and understand. However, why do you need to be able to read identity / sequence numbers? They are generally only supposed to be used for creating uniqueness in a table where there’s no readily-available single point of uniqueness, and so shouldn’t (generally) be exposed. (Would you like it if you were a customer of an organisation and found that your customerID was 3? Just a thought…)

If you start your auto-numbering IDENTITY at one (or zero, I know), you’re effectively ruling out the use of half the number range available in that type (unless you’re using tinyint, which runs 0-255).

The new SEQUENCE field, however, allows you to get the best of both worlds, such as:

create SEQUENCE SmallIntCycleTest
    AS SmallInt
    START WITH 1
    MAXVALUE 32767
    CYCLE
    MINVALUE -32768

(I’ve changed the order of arguments to make it a bit easier to understand). This gives us a smallint (ie 16 bit) number range that starts at 1 (for those that like the readable numbers), goes up to 32767, then loops back to -32768 and starts over.

Ah. The downside – the CYCLE parameter indicates that the sequence will go round. This will mean that you will get duplicate IDs issued. But you should be enforcing uniqueness within the appropriate table anyway, right? Assuming, of course, you’re not using the same sequence to populate multiple tables, and that’s where it gets a bit trickier.

The other thing to be aware of is the behaviour of the wrap-around. Consider this:

create SEQUENCE SmallIntCycleTest
    AS SmallInt
    START WITH 0
    INCREMENT BY 10000
    MINVALUE -32768
    MAXVALUE 32767
    CYCLE
    NO CACHE

It’s a little artificial with an increment of 10000, but I wondered what would happen at the loop-around…

SELECT name, minimum_value, maximum_value, current_value FROM sys.sequences where name = 'SmallintCycleTest'
select NEXT VALUE FOR SmallIntCycleTest
select NEXT VALUE FOR SmallIntCycleTest
select NEXT VALUE FOR SmallIntCycleTest
select NEXT VALUE FOR SmallIntCycleTest
SELECT name, minimum_value, maximum_value, current_value FROM sys.sequences where name = 'SmallintCycleTest'
select NEXT VALUE FOR SmallIntCycleTest
SELECT name, minimum_value, maximum_value, current_value FROM sys.sequences where name = 'SmallintCycleTest'
select NEXT VALUE FOR SmallIntCycleTest
SELECT name, minimum_value, maximum_value, current_value FROM sys.sequences where name = 'SmallintCycleTest'
DROP SEQUENCE SmallintCycleTest

So, as you can see, the loop back hasn’t incremented the sequence by 10000 (with appropriate offsets) when it overflowed, but went to the minimum value permitted by the sequence. So if you want nice tidy ranges, you’ll have to be a bit more careful about how you set them up, and you can’t rely on “clever” design to make sure your passes through the range don’t overlap.

Changing a sequence

Fortunately, there’s the ALTER SEQUENCE statement, which allows you to change the way a SEQUENCE behaves after it has been created.

Disclaimer

Note that this is discussing Denali CTP3, and, as such, information posted herein may not be accurate for the final release of the next version of SQL Server. Note also that the sequences discussed here are for example purposes only, and really shouldn’t be used for real!

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s