Difference between sequence and identity in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 135 of sequence of a
tutorial in this video we'll discuss the
difference between the sequence object
and identity property in sequel server
this is continuation to part 134 so
please watch what 134 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 two which we will discuss in this
video identity property is a table
column property meaning it is tied to
the table but as the sequence object is
a user-defined database object and it's
not tied to any specific table meaning
its value can be shared by multiple
tables look at the create table
statement right here this is creating
employees table and look at this
identity property this is tied to the ID
column of this table now look at this
create sequence statement this is
creating a sequence object but this is
not tied to any specific table meaning
this sequence object can be shared
across multiple tables now let's look at
an example of sharing this sequence
object with these two tables customers
and users notice both the tables have
got ID column in them now what we want
to do is use this sequence object right
here to generate the values for the ID
column in both the tables and for that I
have written five insert statements
right here the first two are for
inserting values into customers table
and these three are for inserting values
into users table and look at what we are
supplying as the value for the ID column
we are using the next value for clause
on the sequence object which is going to
generate the next sequence value from
that sequence object so let's execute
all these five insert statements and now
let's select the data from both the
tables to make sure the same sequence
object is being shared by both of them
look at the ID column values within the
customers table one and two within the
users table it's three four and five so
this proves that the same sequence
object is being shared by both these
tables
now to generate the next identity
property value arrow has to be
physically inserted into the table
whereas with the sequence object there
is no need to insert a row into the
table to generate the next sequence
object value you can simply use the next
value for Clause to generate the next
sequence object value so here we have
the employees table and we have the
identity property on the ID column so if
we have to generate the next identity
value we have to physically insert row
into that employee's table you know at
that point when the row is inserted for
the ID identity property the value will
be generated whereas if you want to
generate the next value from the
sequence object you don't have to
physically insert a row into the table
you simply can use next value for Clause
so when I execute this query we should
get the next value from the sequence
object maximum value for the identity
property cannot be specified the maximum
value will be the maximum value of the
corresponding column data type so if you
look at the identity property this is
defined on a column and that column has
got a data type so there is no way for
us to specify what is going to be the
maximum value for the identity property
the maximum value will be the maximum
value of the data type in this case it
will be the maximum value of the integer
data type whereas with the sequence
object we can specify the maximum value
using the max value option now this max
value option is optional so if we don't
specify that option then the maximum
value of the sequence object will behave
like the maximum value of the identity
property in the sense that it will use
the maximum value of the data type of
that sequence object identity property
does not have any option to
automatically restart the identity
values with a sequence object we can use
the cycle option to specify whether the
sequence should restart automatically
when the maximum value for an increment
sequence object is reached or when
minimum value for decrementing sequence
object is reached so let's quickly look
at that example so if you look at the
create sequence statement right here
notice they using the cycle option so we
are starting with one incrementing by
one and the max value is going to be
five so when the max value is reached
since we have used the cycle option it's
automatically going to restart at the
minimum value that is one so let's drop
the sequence object that we already have
and let's create this sequence object
with this cycle option and look at
what's going to happen as we reach the
maximum value one two three four five
that's the maximum value now when I ask
for the next value again look at that it
automatically restarts at the minimum
value we don't have that kind of an
option with the identity property so
here are some of the differences between
the two arrays the identity property and
the sequence object thank you for
listening and have a great day