1. Industry
Send to a Friend via Email

Your suggestion is on its way!

An email with a link to:

http://newtech.about.com/od/databasemanagement/a/Things-A-Developer-Needs-To-Know-About-Sequences-In-Microsoft-Sql-Server-2011.htm

was emailed to:

Thanks for sharing About.com with others!

Things a Developer Needs to Know About Sequences in Microsoft SQL Server 2011

By

Photo © Microsoft

Sequences are new to SQL Server 2011 (Denali). The basic concept of the Sequence Object in the upcoming release of SQL Server is simple but there are some things you need to know about the Sequence Object to turn you into a SQL Server Sequence expert.

SQL Server 2011 Sequence - Full Syntax

You need to understand the full syntax of the Sequence Object in SQL Server 2011 (Denali). The following Transact-SQL is from SQL Server 2011 Books Online from MSDN.
  • CREATE SEQUENCE: Create sequence creates a sequence object and sets its properties.

      CREATE SEQUENCE [schema_name . ] sequence_name
      [ <sequence_property_assignment> [ ,…n ] ]
      [ ; ]

      <sequence_property_assignment>::=
      {
      [ AS { built_in_integer_type | user-defined_integer_type } ]
      | START WITH <constant>
      | INCREMENT BY <constant>
      | { MINVALUE <constant> | NO MINVALUE }
      | { MAXVALUE <constant> | NO MAXVALUE }
      | { CYCLE | NO CYCLE }
      | { CACHE [<constant> ] | NO CACHE }
      }

  • ALTER SEQUENCE: As the name implys, alter sequence modifies a sequence object that already exists.

    ALTER SEQUENCE [schema_name. ] sequence_name
    [ <sequence_property_update> [ ,…n ] ]
    [ ; ]

    <sequence_property_update>::=
    {
    { RESTART [ WITH <constant> ] }
    | INCREMENT BY <constant>
    | { MINVALUE <constant> | NO MINVALUE }
    | { MAXVALUE <constant> | NO MAXVALUE }
    | { CYCLE | NO CYCLE }
    | { CACHE [ <constant> ] | NO CACHE }
    }

  • DROP SEQUENCE: Drop sequence removes the sequence from the server.

      DROP SEQUENCE { [ database_name . [ schema_name ] . | schema_name. ] sequence_name } [ ,...n ]
      [ ; ]

  • NEXT VALUE FOR: The next value for code generates the sequence number for the the sequence object.

    NEXT VALUE FOR [ database_name . ] [ schema_name . ] sequence_name [ OVER (<over_order_by_clause>) ]

Viewing Details About Sequences on Your SQL Server

You can query SQL Server to show the sequences that are in a particular database. Here is the syntax and the fields you get when you execute the query.

SELECT * FROM sys.sequences

The output of this query includes the following data:

  • name
  • object_id
  • principal_id
  • schema_id
  • parent_object_id
  • type
  • type_desc
  • create_date
  • modify_date
  • is_ms_shipped
  • is_published
  • is_schema_published
  • start_value
  • increment
  • minimum_value
  • maximum_value
  • is_cycling
  • is_cached
  • cache_size
  • system_type_id
  • user_type_id
  • precision
  • scale
  • current_value
  • is_exhausted

Minimum and Maximum Value for Sequences

The minimum and maximum values for a sequence are set in the create sequence command. MINVALUE is the directive to set the minumum value and MAXVALUE is the directive to set the maximum value. These values provide a boundry for the sequence. You can specify what happens when the boundry is exceeded which is the next sequence concept.

Cycle Option in Sequence

You can use the CYCLE option to control what happends when the MAXVALUE is exceeded. If you don't set this option and you exceed the MAXVALUE in a sequence, an exception will be thrown by SQL Server 2011. The 2 options are CYCLE and NO CYCLE.

  • Using the CYCLE option tells SQL Server to reset the sequence to the minimum value if the maximum value is exceeded. Therefore, the sequence is "cycled".
  • Using the NO CYCLE option tells SQL Server to throw an exception when the maximum value is exceeded.

Using OVER to Sort Results Before Generating Sequences Number

There may be times where you want to apply the sequence numbering based on a particular sort.

The following example (from Microsoft MSDN) uses the OVER clause to sort the result set by Name before it adds the sequence number column.

USE AdventureWorks2008R2 ;
GO
CREATE SCHEMA Samples ;
GO
CREATE SEQUENCE Samples.IDLabel AS tinyint START WITH 1 INCREMENT BY 1 ;
GO
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product WHERE Name LIKE '%nut%' ;

Resetting Sequence Using Alter Sequence

There will be time when you need to reset your sequence. It can be accomplished easily using the ALTER SEQUENCE with the RESET option. In the example, the Sequence MySeq is reset to 1.

ALTER SEQUENCE MySeq
RESTART WITH 1 ;

  1. About.com
  2. Industry
  3. New Tech
  4. Database Management
  5. Things a Developer Needs to Know About Sequences in Microsoft SQL Server 2011 (Denali)

©2014 About.com. All rights reserved.