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: Open Microsoft SQL Server in the system. In the Object Explorer window, expand the database. Right click Sequences, click New Sequence. Add the New Sequence dialog box and values in the controls. Type Sequence Name in the textbox. Click on the Browse button, add the appropriate in the schema name In the combo box, select the data type required for the input Add the values in start value text box, increment by the value. 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. When an application needs sharing of series of numbers in multiple tables and columns in a table The number is needed for an application before the insert operation is performed on the table The number series must restart when the required number is accessed. When multiple numbers are to be assigned simultaneously, the concept is useful 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.