SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. They allow administrators to carry out essential functions like monitoring, diagnostics and inventory preparations. This leads one to think that whenever a new functionality is introduced into SQL Server, corresponding DMVs and Catalog Views must either be updated and/or new ones introduced.
One of the major enhancements in SQL Server 2012 is the SEQUENCE (refer the “Further Reading” section for more information on Sequences and their usage). In this post, I will be studying the use of a related Catalog View: sys.sequences. Needless to say, this Catalog View is new to SQL Server 2012.
sys.sequences
To get started, let me create a sequence and use it to populate a result set (re-using Pinal’s example here):
USE AdventureWorks2012
GO
CREATE SEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
GO
— First Run
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO
Now, I will execute the following query which fetches all records from the new Catalog View – sys.sequences.
SELECT ss.name,
ss.object_id,
ss.principal_id,
ss.schema_id,
ss.parent_object_id,
ss.type,
ss.type_desc,
ss.create_date,
ss.modify_date,
ss.is_ms_shipped,
ss.is_published,
ss.is_schema_published,
ss.start_value,
ss.increment,
ss.minimum_value,
ss.maximum_value,
ss.is_cycling,
ss.is_cached,
ss.cache_size,
ss.system_type_id,
ss.user_type_id,
ss.precision,
ss.scale,
ss.current_value,
ss.is_exhausted
FROM sys.sequences AS ss
As we can see from the output, this Catalog View returns one record for each sequence object in the database. Below is a detailed summary of the most important columns with respect to the values supplied in the CREATE SEQUENCE statement:
- start_value – Value defined in the START WITH clause
- increment – Value defined in the INCREMENT BY clause
- minimum_value – Value defined in the MINVALUE clause
- maximum_value – Value defined in the MAXVALUE clause
- is_cycling – 1 if CYCLE clause has a value of 1, else 0
- is_cached – 1 if CACHE clause has a value of 1, else 0
- cache_size – The cache size defined during sequence creation
- system_type_id – A sequence can be created for multiple data-types. This column returns the ID of the system type for sequence objects’ data type
- user_type_id – This column returns the ID of the multiplied type for sequence objects’ data type
- precision – (self-explanatory)
- scale– (self-explanatory)
- currrent_value– This contains the last obligated value of the sequence.
- is_exhausted – A value of 0 means that more values can be generated from the system and that a manual re-cycle of the Sequence is not required
Further Reading:
- From Pinal Dave, a.k.a. “SQLAuthority” (B|T)
- Introduction to SEQUENCE
- SEQUENCE is NOT same as IDENTITY
- Fun with SEQUENCE (Part 1)
- Fun with SEQUENCE (Part 2)
- Fun with SEQUENCE (Part 3)
- Introduction to SEQUENCE
- From Vinod Kumar, a.k.a. “Extreme Experts” (B|T)
- CREATE SEQUENCE BOL Reference: http://msdn.microsoft.com/en-us/library/ff878091.aspx
- sys.sequences BOL Reference: http://msdn.microsoft.com/en-us/library/ff877934.aspx
Until we meet next time,
SQLTwins by Nakul Vachhrajani 