- 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
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?
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).
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.
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!