Archive
SQL Server Denali – Sequences
Another new programmability feature added in the Denali CTP is ‘Sequences’ – a concept very familiar to those of us who already deal with Oracle, but an unusual addition for SQL Server and one that makes me scratch my head thinking – why? We already have the identity column feature available to us within SQL Server but not available within Oracle, thus the need for sequences in Oracle. When using any identity / numeric key / FK mechanism it is important that the actual value for this identity has no actual relation to the data it represents other than an arbitrary number representing the row, if anything it would be a bad design to rely on the identity value be in sequence, or contiguous in any way. In SQL Server it is not guaranteed to be contiguous at all – transaction rollback or a value other than 1 for the increment for example will prevent it.
Sequences are primarily for when you wish to know a number in advance of using it, or perhaps you wish to use the same number for a number of records spread across tables (and thus relate them using that number.)
Looking at the syntax you can see that the SQL Server and Oracle Syntax are very similar and share the same keywords.
SQL Server Syntax:
CREATE SEQUENCE [schema_name . ] sequence_name [ [ ,…n ] ] [ ; ]
::= {[ AS { built_in_integer_type | user-defined_integer_type}]
START WITH
INCREMENT BY
{ MINVALUE | NO MINVALUE }
{ MAXVALUE | NO MAXVALUE }
{ CYCLE | NO CYCLE }
{ CACHE [ ] | NO CACHE } }
Oracle Syntax:
CREATE SEQUENCE INCREMENT BY START WITH MAXVALUE / NOMAXVALUE MINVALUE / NOMINVALUE CYCLE / NOCYCLE CACHE <#> / NOCACHE ORDER / NOORDER
Most of the keywords are self-explanatory, and from a comparison of syntax you can see that SQL Server and Oracle are pretty similar in terms of the syntax.
Most of the keywords are pretty self-explanatory, the one that makes me cringe the most is CYCLE – It’s bad enough using a sequence number instead of an identity, but even worse when you consider that it may not be unique. The advice there is to create an additional unique index on the field to prevent an insertion / updated from taking a duplicate – but that seems like a bit of a ‘fudge’, and instead of solving the real problem, works around it.
To add to the weirdness of the construct, you can even ask for a sequence based on an OVER clause, using the adventure works database as an example I created a sequence:
create sequence testSequence as integer start with 1 increment by 1 minvalue 1 maxvalue 10000
And then used it within a select statement as follows:
select next value for testSequence over (order by Name asc) as id, Name from Production.Product
The results come back:
1 Adjustable Race 2 All-Purpose Bike Stand 3 AWC Logo Cap 4 BB Ball Bearing ...
In case you were thinking that was relatively useful, when you re-run the command, you of course are returned a different set of numbers, as the sequence does not restart, making this one of the weirdest features I have seen.
505 Adjustable Race 506 All-Purpose Bike Stand 507 AWC Logo Cap 508 BB Ball Bearing ...
If you attempt to place the order on the outside in the following manner, SQL Server will just throw an error.
select next value for testSequence as id, Name from Production.Product order by Name asc Msg 11723, Level 15, State 1, Line 1 NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.
And to round off the errors you can expect to see when using this, when you run the sequence out of values, you will get:
Msg 11728, Level 16, State 1, Line 1 The sequence object 'testSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
Try create a sequence based on a numeric or decimal with some scale, such as numeric(6,2):
Msg 11702, Level 16, State 2, Line 1 The sequence object 'testSequence' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.
Or if you fail to get your starting value within the min and max boundaries you are setting:
Msg 11703, Level 16, State 1, Line 1 The start value for sequence object 'testSequence' must be between the minimum and maximum value of the sequence object.
Overall sequences remain a bit of a niche feature for me in SQL Server, I just can not see any normal everyday activity needing to use them, although it would make porting of applications between Oracle and SQL Server a bit easier since they will both be able to use them.
In that kind of situation though I would still prefer the GUID mechanisms that we have available to us. They have the same benefits of being able to know a record ID in advance of using it as well as the ability to be stored in either database. It also has the added advantage of being able to be created whilst offline from the database, something a sequence can not do.
Finding Next Identity Value, and a Wild Goose Chase.
A question asked on stack overflow was to find the next identity value that would occur on a table, without being required to add a record to work it out. The problem lies in that if the highest row is deleted, the number is not reused so any answers using the existing rows can be incorrect.
Logically the value must be stored, so first place I checked was the DMV’s. That stores the currently used value, but does not store the next value.
The wild goose chase started there…
- I used the dedicated admin console to pull all the system tables, expecting it to be in sys.syshobtcolumns, no joy. Dumped the whole system table contents before and after an insert looking for the difference and didn’t spot it.
- Took a dump of every page in the file before and after inserting a new row and having the textural dumps compared in a text comparison application, still no joy.
- Started dumping out log records using the following script and pulled the identity calls:
select * from ::fn_dblog(null, null) where operation = 'LOP_IDENT_NEWVAL'
- After a couple of hours running around the pages trying to find it, I realised I should of stuck with the DMV and I really went the wrong way around it.
The DMV has the right answer it seems, but as two fields you have to combine to get the answer.
create table foo (MyID int identity not null, MyField char(10)) insert into foo values ('test') go 10 -- Inserted 10 rows select Convert(varchar(8),increment_value) as IncrementValue, Convert(varchar(8),last_value) as LastValue from sys.identity_columns where name ='myid' -- insert another row insert into foo values ('test') -- check the values again select Convert(varchar(8),increment_value) as IncrementValue, Convert(varchar(8),last_value) as LastValue from sys.identity_columns where name ='myid' -- delete the rows delete from foo -- check the DMV again select Convert(varchar(8),increment_value) as IncrementValue, Convert(varchar(8),last_value) as LastValue from sys.identity_columns where name ='myid' -- value is currently 11 and increment is 1, so the next insert gets 12 insert into foo values ('test') select * from foo Result: MyID MyField ----------- ---------- 12 test (1 row(s) affected)
So adding the increment to the last value will predict the next value correctly, assuming someone else does not grab it in the mean time which is why it is not a good idea to use in code, but if you need to investigate a table and want to know what it thinks is next, without actually inserting a row and affecting the table, then it is useful.
All that and the easy way is then:
select ident_current(‘foo’) + ident_incr(‘foo’)
Ah well, was fun investigating it – but what a wild goose chase to find it was an easy answer.
SQL Fascination 