SEQUENCE is introduced with SQL Server 2012 to replace IDENTITY worries of the users. There are two important facts about SEQUENCE.
1. Following script will create a SEQUENCE named seqTest and in the line 7, it requests for the next value which will be the first value.
What do you think you will get as the value? Many answers will be either 0 or 1. But the value is
-9223372036854775808. I don’t think you guess it.
Let us go into the details. When you create a SEQUENCE without specifying the data type by default it will take BIGINT as the data type. If you didn’t specify the starting number it will start from the least value which is the –9223372036854775808 (least value for BIGINT is
-9,223,372,036,854,775,808).
Let us look at another scenario with following script.
What do you think about the above script? Now, it’s type is SMALLINT while it has increment with –1. Since we have not specified any start values, it will start from the -32,768 which is the least value for SMALLINT data type and since our sequence specifies to INCREMENT BY –1, you will say that above script will generate an error. Sorry, you got it wrong again. Value will be 32767. When you create a sequence in, it will use the entire range available for the given data type unless you specify a range to use with the MINVALUE and MAXVALUE arguments. In addition to this, SQL Server will check whether the sequence is ascending or descending, and start the sequence at the minimum (ascending) or maximum (descending) value unless you use the START WITH argument.
Read more about SEQUENCE at http://msdn.microsoft.com/en-us/library/ff878091(v=SQL.110).aspx
LinkedIn Group: Microsoft MVP - Professional Group
ReplyDeleteSQLServer Sequences are incredibly handy, but the basic premise has been done using a table and a stored proc for many years (decades)...OF course, the optimizations provided by making this a "first class citizen" of SQLServer can be a big difference, but the tradeoff is that you are locked to 2012.....
Posted by David V. Corbin