• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server Sequence

SQL Server Sequence

Learning Objective

The objective of this tutorial is to teach you how to use the Sequence object in SQL Server to generate a sequence of numeric values in a particular order. 

What is a Sequence

A Sequence is a list of number which is in particular order. In Sequence, ordering of the number is important. For example, the (1,2,3,4) is a sequence however the (4,3,2,1) is a entirely difference sequence although all the numbers in both the Sequence are same.

In SQL Server, a sequence is a user-defined object that generates a sequence of numbers in a particular order. A sequence of numeric values can be in ascending or descending order at a defined interval and may cycle if requested.

SQL Server CREATE Sequence syntax

CREATE SEQUENCE allows you to create a new sequence in SQL Server. The syntax for creating a new sequence is shown below:

CREATE SEQUENCE [schema_name.] sequence_name
   [ AS integer_type ]
   [ START WITH start_value ]
   [ INCREMENT BY increment_value ]
   [ { MINVALUE [ min_value ] } | { NO MINVALUE } ]
   [ { MAXVALUE [ max_value ] } | { NO MAXVALUE } ]
   [ CYCLE | { NO CYCLE } ]
   [ { CACHE [ cache_size ] } | { NO CACHE } ];

In this syntax,

  • schema_name – Name of the schema where the sequence belongs.
  • sequence_name – Name of the sequence you wish to keep.
  • AS integer_type – You can use any valid inter type for sequence. For example, TINYINT, SMALLINT, INT, BIGINT, DECIMAL or NUMERIC with a scale of 0. Default is BIGINT.
  • START WITH start_value – Defines the start value of the sequence. The start value must be within the range of min_value and max_value. The default value of start_value is min_value for an ascending sequence and max_value for a descending sequence.
  • INCREMENT BY increment_value – Defines the increment value of the sequence object when you call the NEXT VALUE FOR function. In case the increment_value is negative then it is a descending sequence otherwise ascending sequence. Remember that increment_value can’t be 0.
  • [ { MINVALUE [ min_value ] } | { NO MINVALUE } ] – Defines the lower bound of the sequence objects. In case of TINYINT, default value is 0 and for all other datatypes it is negative.
  • [ { MAXVALUE [ max_value ] } | { NO MAXVALUE } ] – Defines the upper bound of the sequence object. Default is maximum range of the data types of the sequence objects.
  • [ CYCLE | { NO CYCLE } ] – You can use the CYCLE clause if you want the value of the sequence to be restart from the min_value for the ascending sequence object and max_value for the descending sequence object or throw an exception when the range of min_value and max_value exceeds. By default, SQL Server uses NO CYCLE for new sequence objects.
  • [ { CACHE [ cache_size ] } | { NO CACHE } ] – This is used to define the number of sequence values to cache to improve the performance of the sequence by reducing the numbers of disk I/O. By default, SQL Server uses NO CACHE for a new sequence objects.

SQL Server SEQUENCE Examples

To understand the concept of sequence with more clarity, let’s check some examples of sequence.

1) Creating a simple sequence

You can create a new sequence by using CREATE SEQUENCE statement. In this example, we have created a sequence named product_id with start value 100 and increment value 10 and the data type is integer (INT).

CREATE SEQUENCE product_id
AS INT
START WITH 100
INCREMENT BY 10;

You can also view the sequence objects in SQL Server Management Studio by expanding Programmability > Sequences as shown below:

SQL Server Sequence

The below statement will return the current value of the sequence object.

SELECT NEXT VALUE FOR product_id;

Output

Current_value
100
(1 row affected)

In the above example, NEXT VALUE FOR generates a sequence for the object product_id.

Each time you executes the above statement, the sequence will increment by 10.

SELECT NEXT VALUE FOR product_id;

This time, the above statement will generate the output 110 as below.

Output

Current_value
110
(1 row affected)

2) Using a Sequence in a Single Table

First, create a new schema generalstore as follows.

CREATE SCHEMA generalstore;

Next, create a new table named product in genrealstore schema.

CREATE TABLE generalstore.product
(
product_id INT PRIMARY KEY,
product_name nvarchar(100) NOT NULL,
quantity INT NOT NULL
);

Now, create a sequence name product_id_seq as below.

CREATE SEQUENCE product_id_seq
AS INT
START WITH 100
INCREMENT BY 1;

After that, insert three rows into the generalstore.product table. We will use product_id_seq for inserting new product if into the table.

INSERT INTO generalstore.product
(product_id,
product_name,
quantity)
VALUES
(NEXT VALUE FOR product_id_seq,'Milk',20);

INSERT INTO generalstore.product
(product_id,
product_name,
quantity)
VALUES
(NEXT VALUE FOR product_id_seq,'Bread',25);

INSERT INTO generalstore.product
(product_id,
product_name,
quantity)
VALUES
(NEXT VALUE FOR product_id_seq,'Eggs',160);

Finally, you can check the content of the product table using select query.

select * from generalstore.product;

You can notice that the product_id column has been inserted using the product_id_seq sequence. Here first value is 100 and incremented by 1 for each new row.

Was this tutorial helpful?
YesNo
« Previous: SQL Server IDENTITY column
SQL Server Clustered Index :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.