The Release of SQL Server 2012 introduces the sequence object to the world of SQL Server. A sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table. A few interesting differences between the two are;
A detailed list of differences between Identity and Sequence objects can be found here.
You can use the SQL Server Management Studio GUI or a TSQL statement to create a sequence object.
CREATE SEQUENCE dbo.demo_2012_sequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 CACHE 20 CYCLE;
Some of the interesting aspects of the Create Sequence statement are;
Once the sequence object has been created, you can use the Next Value For function, to generate a sequence value from it. It’s a non-deterministic function and can be used in stored procedures and triggers. When using it in a query, please be aware of the limitations and restrictions on its usage; the most notable one being the restriction on using it in queries that contain SET Operators like UNION, UNION ALL, etc. It can also be used in a Default Constraint as well as an OVER ORDER BY Clause. A complete list of the restrictions and limitations on its usage is available here.
You can get one or more values out of the sequence object. You can also ALTER the sequence object to RESTART it, or change many of its properties. Let’s look at some examples.
-- get one value out of the sequence object at one time SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS seq_no; GO SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS next_seq_no; GO
And the results look like:
seq_no ----------- 1 (1 row(s) affected) next_seq_no ----------- 2 (1 row(s) affected)
-- creating a table and populate it with 5 rows CREATE TABLE #demo_sequence (demo_name VARCHAR(12)); INSERT INTO #demo_sequence VALUES ('row_1'), ('row_2'), ('row_3'), ('row_4'), ('row_5'); GO -- Restart the sequence from 1 ALTER SEQUENCE dbo.demo_2012_sequence RESTART WITH 1 INCREMENT BY 1; GO -- get 5 values out of the sequence object at 1 time SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS seq_no, demo_name FROM #demo_sequence; GO
seq_no demo_name ----------- ------------ 1 row_1 2 row_2 3 row_3 4 row_4 5 row_5 (5 row(s) affected)
You can also use the system object sys.sp_sequence_get_range to generate and return a number of values, along with some of the metadata related to the range. Let’s look at an example;
DECLARE @range_first_value sql_variant , @range_first_value_output sql_variant, @range_last_value_output sql_variant ; EXEC sp_sequence_get_range @sequence_name = N'[dbo].[demo_2012_sequence]' , @range_size = 4 , @range_first_value = @range_first_value_output OUTPUT , @range_last_value = @range_last_value_output OUTPUT; SELECT @range_first_value_output AS RangeFirstNumber, @range_last_value_output AS RangeLastNumber; GO
RangeFirstNumber RangeLastNumber --------------------- ------------------ 6 9 (1 row(s) affected)
The sys.sequences view can also be used to query metadata for a sequence object. It contains one row for each sequence object in the database.
SELECT current_value, cache_size, is_cached, create_date, is_exhausted FROM sys.sequences WHERE name = 'demo_2012_sequence'; GO
current_value cache_size is_cached is_exhausted --------------- ----------- ----------- ------------ 5 20 1 0 (1 row(s) affected)
In conclusion, the new Sequence Object can provide a viable alternative to Identity, if you are looking for one. It is definitely worth exploring for your next project.
Maheshkumar S Tiwari edited Original. Comment: Added tags
Maheshkumar S Tiwari edited Revision 1. Comment: Added tags
This is good to know. Soon, we will have our first 2012 SQL server in place. Thanks.