This SQL Server tutorial explains, What is Sequence in SQL Server? How to create a sequence in SQL server? And How to use sequence in SQL Server? We will also discuss, the difference between Sequence and Identity in SQL Server.
SharePoint 2016 Tutorial Contents
Sequence in SQL Server
A Sequence is an object which is introduced in SQL Server 2012. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created.
The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.
This is like a global sequence generator which is not specific to any table rather it can be used in any table.
Create a sequence in SQL Server
A Sequence can be created using SQL Server Management Studio as well as using TSQL Statement.
Through SQL Server Management Studio:
To Create a Sequence through SQL Server Management Studio, From your database -> Programmability -> Sequences -> Then Right click and Click on Create New Sequence.
Through TSQL Statements:
CREATE SEQUENCE MyDBSequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE
This will create a sequence that will start from 1 and it will go on by incrementing 1 everytime.
How to use sequence in SQL Server?
Now we can use the Sequence like below:
CREATE TABLE Users ( UserID INT NOT NULL, UserName VARCHAR(256) NOT NULL ) go INSERT Users (UserID, UserName) VALUES (NEXT VALUE FOR MyDBSequence, ‘Bijay’), (NEXT VALUE FOR MyDBSequence, ‘Sanjay’), (NEXT VALUE FOR MyDBSequence, ‘Tamanna’)
Now if you will retrieve the data from the Users table then the ID will come as 1,2,3.
Now suppose you want to use in another table then it will be like below:
CREATE TABLE Students ( StudentID INT NOT NULL, StudentName VARCHAR(256) NOT NULL ) go INSERT Students (StudentID, StudentName) VALUES (NEXT VALUE FOR MyDBSequence, ‘Raj’), (NEXT VALUE FOR MyDBSequence, ‘Gopal’), (NEXT VALUE FOR MyDBSequence, ‘Pooja’)
Here if you will retrieve the data from the Students table then the StudentID will come as 4,5,6.
Difference between Sequence and Identity in Sql Server
Let us discuss some of the difference between Sequence and Identity in Sql Server.
Below are some of the differences between Sequence and Identity Column in SQL Server:
- Identity columns are specific to tables inside a database, whereas Sequence is not for any table specific, it can be used in the whole database.
- Identity columns values generated when a row inserted to a table, but you can get the sequence number before inserting a row.
- You can not change the value of the Identity column once inserted, but the sequence number can be changed after insertion into the table.
- Identity column guarantees uniqueness inside the table, but in the case of Sequence,e you need to create a unique index on the column.
- In the case of the Identity column, you cannot define the minimum and maximum values, whether to allow cycling and caching options or not. But these things can be archived through Sequence in SQL Server 2008.
You may like following sql server tutorials:
- SQL Server stored procedure tutorial
- Difference between clustered and non clustered index SQL server
- Difference between primary key and unique key in SQL Server
- Difference between primary key and foreign key in SQL server
- Delete and create a new log file with minimum size in SQL Server
- Enable remote connections for SQL Server
- SQL Server joins tutorial
- SQL Server Functions Tutorial
- SQL DML and DDL commands
- How to Setting maximum memory in SQL server?
- Create Configure and Use SQL Server Database using Microsoft Azure
- How to create SQL Database in Microsoft Azure Portal
- Connect to Azure SQL database from management studio
I hope this SQL server tutorial explains, a sequence in SQL Server, how to create a sequence in SQL server? How to use a sequence in SQL server and finally, we will discuss the difference between Sequence and Identity in SQL Server.
I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.