Sequences in SQL Server

Discussion in 'SQL Server' started by MinalS, Mar 2, 2015.

  1. MinalS

    MinalS New Member

    Joined:
    Jul 8, 2014
    Messages:
    138
    Likes Received:
    32
    Trophy Points:
    0
    The CREATE SEQUENCE command is used for creating the sequence in SQL server.

    Code:
    
    CREATE SEQUENCE [ schema_name.] sequence_name
    [ 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 } ]
    [ ; ]
    
    
    Here,

    Name : It defines a sequence is a database object.

    [ built_in_integer_type | user_defined_integer_type ] states the integer type of a sequence. The types that can be used are smallint, tinyint, int, bigint, decimal.

    START WITH <constant> : It is the value returned by the object of sequence. The START value must be less than or equal to the value of the object.

    INCREMENT BY <constant>: The value for incrementing the sequence object for every call to the next value. The order is descending if the value is negative. The default value for the object is 1.

    [ MINVALUE <constant> | NO MINVALUE ] : It states the bounds for the sequence object. The default minimum value for the sequence object is defined by the data type used.

    [ MAXVALUE <constant> | NO MAXVALUE ] : It states the bounds for the sequence object. The default maximum value for the sequence object is defined by the data type used.

    [ CYCLE | NO CYCLE ] : It states that the object to restart from minimum value or an exception when the value is exceeded.

    [ CACHE [ <constant> ] | NO CACHE: It increases the application performance using the sequence objects by reducing the disk input/output generating the sequence number.

    Creating Sequence in SQL Server

    For creating the sequence in SQL Server, follow the steps as mentioned below:
    1. Open Microsoft SQL Server in the system.
    2. In the Object Explorer window, expand the database.
    3. Right click Sequences, click New Sequence.

      [​IMG]
    4. Add the New Sequence dialog box and values in the controls.

      [​IMG]
    5. Type Sequence Name in the textbox.
    6. Click on the Browse button, add the appropriate in the schema name
    7. In the combo box, select the data type required for the input
    8. Add the values in start value text box, increment by the value.
    9. Click OK.

    Programmatically creating sequence in SQL Server



    Add the following code in the code window.

    Code:
    
    CREATE SEQUENCE studentseq
    AS Int
    START WITH 1,
    INCREMENT BY 1;
    GO
    
    
    Press F5 and execute the code in the application.

    Once the sequence is created, an object for it is formed. User can use the object in the new table. When the user creates a field in the table, depending on the compatibility the sequence of the string is specified.

    Create the table Sales in the database. User can add appropriate fields to it.

    Code:
    
    CREATE TABLE Sales
    (
    	OrderNo int,
    	OrderName varchar(20),
    	Price money
    );
    GO
    
    
    The following syntax is used for specifying the value for the sequence.

    Code:
    
    NEXT VALUE FOR [ schema_name. ] sequence_name
    
    Create Schema Details
    GO
    CREATE SEQUENCE Details.Items
    AS int
    	START WITH 1001
    	INCREMENT BY 1;
    GO
    
    CREATE TABLE Details.AddValues
    (
    	ItemCode int,
    	Name varchar(20),
    	Cost money
    );
    GO
    
    INSERT INTO Details.AddValues VALUES ( NEXT VALUE FOR Details.Details.Items , ‘Pen’, 30.00),
    ( NEXT VALUE FOR Details.Details.Items , ‘Laptop’, 100000.00),
    ( NEXT VALUE FOR Details.Details.Items , ‘Books’, 5000.00),
    ( NEXT VALUE FOR Details.Details.Items , ‘Mobile’, 40000.00),
    GO
    
    Creating Sequence using arguments

    User can create a sequence using the arguments. The range for the arguments can be specified in the declaration itself. The code snippet mentioned below demonstrates the use of arguments in a sequence.

    Code:
    
    CREATE SEQUENCE seq1 
    AS decimal (4,0)
    START WITH 120
    INCREMENT BY 10
    MINVALUE 50
    MAXVALUE 250
    CYCLE
    CACHE 4
    ;
    
    
    In the code above, a sequence as seq1 is created. The sequence starts with the value 120 and is incremented by 10 when the number is generated. When the value goes above 250, the sequence starts from 50.

    Execute the statement for viewing the first value of the sequence.

    Code:
    
    SELECT NEXT VALUE FOR seq1;
    
    

    Altering the sequence



    User can modify the sequence object. The use of the CACHE option will alter the sequence of the created one. It will recreate a cache. The sequence is created using the data type returning an integer. The ALTER SEQUENCE statement cannot modify the data type used.

    The syntax for alter sequence is as shown below:

    Code:
    
    ALTER SEQUENCE [ schema_name. ] sequence_name
    	[ RESTART [ WITH <constant> ] ] 
    	[ INCREMENT BY <constant> ] 
    	[ {MINVALUE <constant> | { NO MINVALUE } ]
    	[ {MAXVALUE <constant> | { NO MAXVALUE } ]
    	[ CYCLE | { NO CYCLE } ]
    	[ { CACHE [ <constant> ] } | { NO CACHE } ]
    	[ ; ]
    
    
    The following code snippet demonstrates the alteration of a sequence.

    Code:
    
    	ALTER SEQUENCE seq1
    	RESTART WITH 100 
    	INCREMENT BY 20
    	MINVALUE 100
    	MAX VALUE 200
    	CYCLE 
    	CACHE 4
    
    
    Here, the sequence will not cycle and the function will return an error when the number exceeds 200

    Dropping a Sequence



    User can drop the sequence using the DROP SEQEUNCE statement. It removes the object from the database.

    The syntax for dropping the sequence is as shown below:

    Code:
    
    DROP SEQUENCE { [ database_name . [ schema_name ]. | schema_name } [ ,….n ] 
    [ ; ]
    
    
    Here,

    database_name is the name of the database
    schema_name is the schema name containing the sequence object
    sequence_name is the sequence dropped by the user

    The following code snippet shows the example of dropping the sequence.

    Code:
    
    DROP SEQUENCE seq1;
    GO;
    
    
    Sequence Numbers

    The sequence is the schema bound object creating a sequence of numeric values according to the requirements. The values can be in ascending or descending order. The next value is accessed using the sequence object.

    The sequences can be used instead of the identity columns in the following mentioned conditions.
    1. When an application needs sharing of series of numbers in multiple tables and columns in a table
    2. The number is needed for an application before the insert operation is performed on the table
    3. The number series must restart when the required number is accessed.
    4. When multiple numbers are to be assigned simultaneously, the concept is useful
    5. When the values are to be sorted by another field.
    Using NEXT VALUE FOR

    User can call the NEXT VALUE FOR before inserting the row in a table.
    Consider the following code snippet for demonstrating the functionality.

    Code:
    
    DECLARE @NewID int;
    SET @NewID = NEXT VALUE FOR seq1;
    
    //Perform some operations
    
    INSERT Student ( StudID, StudName)
    VALUES ( @NewID, ‘Sam’ )
    GO
    
    
    In the above snippet, a variable as @NewID is declared. It used the NEXT VALUE FOR function for assigning the variable to the next sequence number. The application performs processing like providing id to the student. The original number is saved for use by the current connection. The INSERT statement adds the id to the student table.

    Creating sequence numbers using OVER clause

    The following code snippet uses the OVER clause for sorting the result set by Course before adding the sequence number.

    Code:
    
    CREATE SCHEMA Data;
    GO
    
    CREATE SEQUENCE Data.Order
    	AS smallint
    	START WITH 1
    	INCREMENT BY 1;
    GO
    
    SELECT NEXT VALUE FOR Data.Order OVER ( ORDER BY Course ) AS CourseID, StudName Marks from Student Where StudName LIKE ‘%ing%’ ;
    
    
    Using Sequence as Defualt

    User uses the NEXT VALUE FOR to specify the value of the column with sequence. If the column needs to be called frequently, the sequence can be set to the default value of the column. The INSERT statement is not required by the user.

    The following code snippet demonstrates the use of default.

    Code:
    
    CREATE TABLE Employee
    ( 
    	EmpID int not null,
    		DEFAULT ( NEXT VALUE FOR EmployeePay )
    	EmpName varchar(20), 
    	Role varchar(40),
    	Location varchar(40)
    );
    GO
    
    INSERT INTO Employee ( EmpName, Role, Location ) VALUES 
    ( ‘101’, ‘John’, ‘Lead’, ‘Mumbai’), 
    ( ‘102’, ‘Steve’, ‘Manager’, ‘London’), 
    GO
    
    
    Execute the code and user can access the required values.
     
    Last edited by a moderator: Jan 21, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice