#0238-SQL Server 2012-DMVs & Catalog Views-sys.sequences


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

image


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:



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s