Suggested Videos
Part 132 - sys.dm_sql_referencing_entities in SQL Server
Part 133 - sp_depends in SQL Server
Part 134 - Sequence object in SQL Server 2012
In this video we will discuss the difference between SEQUENCE and IDENTITY in SQL Server
This is continuation to Part 134. Please watch Part 134 from SQL Server tutorial before proceeding.
Sequence object is similar to the Identity property, in the sense that it generates sequence of numeric values in an ascending order just like the identity property. However there are several differences between the 2 which we will discuss in this video.
Identity property is a table column property meaning it is tied to the table, where as the sequence is a user-defined database object and is not tied to any specific table meaning it's value can be shared by multiple tables.
Example : Identity property tied to the Id column of the Employees table.
Example : Sequence object not tied to any specific table
This means the above sequence object can be used with any table.
Example : Sharing sequence object value with multiple tables.
Step 1 : Create Customers and Users tables
Step 2 : Insert 2 rows into Customers table and 3 rows into Users table. Notice the same sequence object is generating the ID values for both the tables.
Step 3 : Query the tables
Output : Notice the same sequence object has generated the values for ID columns in both the tables
To generate the next identity value, a row has to be inserted into the table, where as with sequence object there is no need to insert a row into the table to generate the next sequence value. You can use NEXT VALUE FOR clause to generate the next sequence value.
Example : Generating Identity value by inserting a row into the table
INSERT INTO Employees VALUES ('Todd', 'Male')
Example : Generating the next sequence value using NEXT VALUE FOR clause.
SELECT NEXT VALUE FOR [dbo].[SequenceObject]
Maximum value for the identity property cannot be specified. The maximum value will be the maximum value of the correspoding column data type. With the sequence object you can use the MAXVALUE option to specify the maximum value. If the MAXVALUE option is not specified for the sequence object, then the maximum value will be the maximum value of it's data type.
Example : Specifying maximum value for the sequence object using the MAXVALUE option
CYCLE option of the Sequence object can be used to specify whether the sequence should restart automatically when the max value (for incrementing sequence object) or min value (for decrementing sequence object) is reached, where as with the Identity property we don't have any such option to automatically restart the identity values.
Example : Specifying the CYCLE option of the Sequence object, so the sequence will restart automatically when the max value is exceeded
Part 132 - sys.dm_sql_referencing_entities in SQL Server
Part 133 - sp_depends in SQL Server
Part 134 - Sequence object in SQL Server 2012
In this video we will discuss the difference between SEQUENCE and IDENTITY in SQL Server
This is continuation to Part 134. Please watch Part 134 from SQL Server tutorial before proceeding.
Sequence object is similar to the Identity property, in the sense that it generates sequence of numeric values in an ascending order just like the identity property. However there are several differences between the 2 which we will discuss in this video.
Identity property is a table column property meaning it is tied to the table, where as the sequence is a user-defined database object and is not tied to any specific table meaning it's value can be shared by multiple tables.
Example : Identity property tied to the Id column of the Employees table.
CREATE TABLE Employees
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
Example : Sequence object not tied to any specific table
CREATE SEQUENCE [dbo].[SequenceObject]
AS INT
START WITH 1
INCREMENT BY 1
This means the above sequence object can be used with any table.
Example : Sharing sequence object value with multiple tables.
Step 1 : Create Customers and Users tables
CREATE TABLE Customers
(
Id INT PRIMARY KEY,
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
GO
CREATE TABLE Users
(
Id INT PRIMARY KEY,
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
GO
Step 2 : Insert 2 rows into Customers table and 3 rows into Users table. Notice the same sequence object is generating the ID values for both the tables.
INSERT INTO Customers VALUES
(NEXT VALUE
for [dbo].[SequenceObject], 'Ben', 'Male')
INSERT INTO Customers VALUES
(NEXT VALUE
for [dbo].[SequenceObject], 'Sara', 'Female')
INSERT INTO Users VALUES
(NEXT VALUE
for [dbo].[SequenceObject], 'Tom', 'Male')
INSERT INTO Users VALUES
(NEXT VALUE
for [dbo].[SequenceObject], 'Pam', 'Female')
INSERT INTO Users VALUES
(NEXT VALUE
for [dbo].[SequenceObject], 'David', 'Male')
GO
Step 3 : Query the tables
SELECT * FROM Customers
SELECT * FROM Users
GO
Output : Notice the same sequence object has generated the values for ID columns in both the tables
To generate the next identity value, a row has to be inserted into the table, where as with sequence object there is no need to insert a row into the table to generate the next sequence value. You can use NEXT VALUE FOR clause to generate the next sequence value.
Example : Generating Identity value by inserting a row into the table
INSERT INTO Employees VALUES ('Todd', 'Male')
Example : Generating the next sequence value using NEXT VALUE FOR clause.
SELECT NEXT VALUE FOR [dbo].[SequenceObject]
Maximum value for the identity property cannot be specified. The maximum value will be the maximum value of the correspoding column data type. With the sequence object you can use the MAXVALUE option to specify the maximum value. If the MAXVALUE option is not specified for the sequence object, then the maximum value will be the maximum value of it's data type.
Example : Specifying maximum value for the sequence object using the MAXVALUE option
CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 1
MAXVALUE 5
CYCLE option of the Sequence object can be used to specify whether the sequence should restart automatically when the max value (for incrementing sequence object) or min value (for decrementing sequence object) is reached, where as with the Identity property we don't have any such option to automatically restart the identity values.
Example : Specifying the CYCLE option of the Sequence object, so the sequence will restart automatically when the max value is exceeded
CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
Hi Venkat,I GOT THIS MESSAGE WHILE CREATING SEQUENCE IN SQL:The sequence object 'S' cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.
ReplyDeleteThis is sql statement:
CREATE SEQUENCE DBO.S
AS INT
START WITH 1
INCREMENT BY 5
MAXVALUE 100
Colud you please clarify me about this message.