Sequence in SQL Server 2012 : Complete guide with example

Sequence in SQL Server 2012 : Complete guide with example

sql

What is Sequence in SQL Server 2012 ?
Well, lets keep it simple so that we understand it easily. Sequence is a user defined, schema bonded database object which generate numeric values according to user defined rules.
We can assign numeric values with either ascending or descending order. The major difference between an Identity Column and Sequence is Identity column’s are associated with specific tables whereas Sequence are not.

Difference Between Identity Column and Sequence?
As we have discussed above the main difference between Identity and Sequence is Identity column is associated with specific table and Sequence are not. Sequence are database objects while Identity column reside inside a table. We can easily find the next value for the sequence where as in Identity column we need to commit the insert query to get the identity value. Identity column values are always incremental and can not be defined in a range or order where as Sequence values can be defined
in a range and has options to set increment in ascending and descending order.

Sequence Syntax:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]

Arguments
tinyint – Range 0 to 255
smallint – Range -32,768 to 32,767
int – Range -2,147,483,648 to 2,147,483,647
bigint – Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
decimal and numeric with a scale of 0.
Any user-defined data type (alias type) that is based on one of the allowed types.
If no data type is provided, the bigint data type is used as the default.

START WITH <constant>
The first value returned by the sequence object. The START value must be a value less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. The default start value for a new sequence object is the minimum value for an ascending sequence object and the maximum value for a descending sequence object.

INCREMENT BY <constant>
Value used to increment (or decrement if negative) the value of the sequence object for each call to the NEXT VALUE FOR function. If the increment is a negative value, the sequence object is descending; otherwise, it is ascending. The increment cannot be 0. The default increment for a new sequence object is 1.

[ MINVALUE <constant> | NO MINVALUE ]
Specifies the bounds for the sequence object. The default minimum value for a new sequence object is the minimum value of the data type of the sequence object. This is zero for the tinyint data type and a negative number for all other data types.

[ MAXVALUE <constant> | NO MAXVALUE ]
Specifies the bounds for the sequence object. The default maximum value for a new sequence object is the maximum value of the data type of the sequence object.

[ CYCLE | NO CYCLE ]
Property that specifies whether the sequence object should restart from the minimum value (or maximum for descending sequence objects) or throw an exception when its minimum or maximum value is exceeded. The default cycle option for new sequence objects is NO CYCLE.
That cycling restarts from the minimum or maximum value, not from the start value.

[ CACHE [<constant> ] | NO CACHE ]
Increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.
For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

Note
If the cache option is enabled without specifying a cache size, the Database Engine will select a size. However, users should not rely upon the selection being consistent. Microsoft might change the method of calculating the cache size without notice.

Default Values for the Arguments:

start_value 9223372036854775808
increment 1
mimimum_value -9223372036854775808
maximum_value 9223372036854775807
is_cycling 0
is_cached 1
current_value -9223372036854775808

Lets Explore more with an Example:

--Lets create a Sequence
CREATE SEQUENCE SEQ
AS INT
START WITH 1
INCREMENT BY 1
GO

Adding Sequence to the Table while Creating a Table:

CREATE TABLE EXEC_EMPLOYEE
(
EMP_ID INT CONSTRAINT DF_EMP_ID DEFAULT NEXT VALUE FOR SEQ,
EMP_NAME VARCHAR(100),
EMP_GENDER BIT,
EMP_SALARY MONEY,
EMP_SHIFT BIT
)
GO

Adding Sequence to the Table:

ALTER TABLE EXEC_EMPLOYEE
ADD CONSTRAINT DF_APP_ID DEFAULT NEXT VALUE FOR SEQ FOR APP_ID

Insert Query using Sequence:

INSERT INTO [dbo].[EXEC_EMPLOYEE] VALUES
(NEXT VALUE FOR DBO.SEQ, 'Sequence Tutorial',0,200000,0)

Thanks for reading, hope you loved it.

Leave a Reply

Your email address will not be published. Required fields are marked *