Sequence object in SQL Server 2012 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 134 of sequel server
tutorial in this video we'll discuss
sequence object in sequence server
sequence object is introduced in sequel
server 2012 it generates sequence of
numeric values in an ascending or
descending order it is the syntax for
creating a sequence object we use create
sequence statement to create a sequence
object so create sequence and then we
specify the schema name dot and the name
for our sequence object and then we use
the ask keyword and then we have to
specify the datatype for the sequence
object the data type can be any of the
built-in integer type it can be tiny and
small and big int decimal etc or it can
also be any of the user-defined integer
type the data type is optional so if we
don't specify a value explicitly then by
default the data type is going to be big
end next we can specify start with this
option specifies the first value to be
returned by the sequence object
increment by this specifies the value to
increment or decrement by the value will
be decremented if a negative value is
specified if this is not clear at the
moment don't worry we'll look at an
example and that should make it clear
min value specifies the minimum value
for the sequence object max value
specifies the maximum value for the
sequence object the cycle option
specifies whether the sequence object
should restart when the maximum value
for the incrementing sequence object has
reached or when the minimum value for
the decrementing sequence object has
reached the default if we don't specify
a value explicitly is no cycle with the
default option the sequence object will
throw an exception when a minimum or
maximum value is exceeded again if this
is not clear don't worry we'll look at
an example and that should make it clear
finally we can use the cash option to
cache the sequence object values for
performance we'll look at an example of
that as well now let's look at an
example of creating an incrementing
sequence so to create a sequence object
we use create sequence statement and
then we specify the schema the schema
here is DBA and the name of our sequence
object is going to be sequence object as
and we are specifying the data type as
integer we don't have to if you don't
specify
I explicitly then by default the
datatype for the sequence object is
going to be big int and then we are
telling the sequence object should start
with a value of 1 and increment it by a
value of 1 I have this exact same code
already typed so when we execute this
the sequence object should be created
the obvious next question is how to
generate the next sequence value to
generate the next sequence value use
next value for Clause so select next
value for this sequence object so when
we execute this since we have told the
sequence object to start with a value of
1 it should start at 1 and every time we
execute this look at that the value is
incremented now let's say you know we
have executed next value 4 and you don't
know what the current value is now you
want to know the current value to know
the current value of the sequence object
you can use this dynamic management view
says dot sequences it has got a lot of
information available so select star
from sistar sequences where name equals
the name of the sequence object so when
we execute that we are going to get a
lot of information look at that they
have the name of the sequence object
when is the sequence object created and
what is the start value what is the
increment value notice start value is 1
increment by 1 what is the minimum and
maximum value look at the minimum and
maximum value we have not specified
minimum and maximum values explicitly
but we have specified the data type the
data type is integer so it is taking by
default the default values of that data
type as the minimum and maximum values
and we also have information about
whether this cycle option is enabled
whether caching is enabled or not and
what is the cache size at the moment we
haven't specified any cache size so it's
now and look at this column right here
current underscore value so the current
value of the sequence object is 5 so
using this column from this dynamic
management view we can find out what is
the current sequence object value so at
the moment the value is 5 so when we
execute this we should get a value of 6
there
now let's say for some reason we want to
reset the sequence value to reset the
sequence value we alter the sequence
object all the sequence the name of the
sequence object and we simply specify
restart with one okay so let's alter the
sequence object and now when we select
next value it should start at a value of
one and every time we execute that it
should increment the value from that
point on now let's look at an example of
using the sequence value in an insert
statement for that what I'm going to do
is create this employees table and look
at the employees table it has got three
columns ID name and gender so I want to
use the sequence object value as the
value for this ID column so let's create
this table first and look at the insert
query right here insert into employees
values and we'll have to supply values
for these three columns so first I'm
supplying the value for the ID column
from the sequence object so we are using
next value for clause here so at the
moment if you look at the next value of
the sequence object it is six so when we
execute these to insert queries for the
first row it should be seven and for the
second row the ID value should be eight
let's execute these two queries and
select the data from the employees table
and notice we get seven and the eight is
expected so you can use that sequence
object value in an insert statement as
well now let's look at an example of
creating a decrementing sequence object
now to create a decrementing sequence
object all you have to do is specify a
negative value for increment by the rest
of the statement stays the same so look
at the example right here create
sequence schema name sequence object
name as int start with 100 and increment
by negative one so it's going to start
at 100 and every time we call next value
for on the sequence object it's going to
reduce the value by negative one so
let's look at that quickly in action so
first let's drop the existing sequence
object and we are going to create
this decrementing sequence object so we
said the value should start at 100 so
when I call next value 4 for the first
time notice the value is 100 now every
time we call it it should reduce by a
value of 1 now let's look at an example
of specifying min and Max values for the
sequence object so far we haven't
specified min and Max values so since we
didn't specify min and Max values it is
taking the default values of the type as
the minimum and maximum values now let's
drop the sequence object that we have
and I'm going to create a sequence
object so it's going to start with a
value of 100 and we are incrementing the
value by 10 every time we call a next
value 4 and minimum value is 100 maximum
value is 150 so let's go ahead and
create this sequence object and now I'm
going to call next value 4 so we told it
to start with value of one hundred and
increment by 10 so first time it should
return 100 and every time we call it
it's going to increment the value now
the interesting thing to keep in mind
here is what happens when it reaches a
value of you know 150 that is the
maximum value so at the moment it's 140
150 and I'm executing it again look at
that we get an error so when the maximum
value is reached by default it is going
to throw an error because remember for
the cycle option the default value is no
cycle so with no cycle option it's going
to throw an error when a maximum value
is reached for the incrementing sequence
object okay now if you want to restart
at the minimum value then set the cycle
option so what I'm going to do is alter
the sequence of object and we are
setting the cycle option so if you don't
set the cycle option when the maximum
value is reached and when you call next
value it throws an error so if you want
to restart at the minimum value then set
the cycle option so let's go ahead and
alter the sequence object and now let's
execute this so it starts at 100 so
every time I execute it increments by 10
so we reach the maximum value 150 and I
next value for again look at that it
restarts at 100 okay no exception now to
improve the performance we can use the
cash option to cash the values of the
sequence object and if you look at the
sequence object right here so we are
basically saying start with one
increment by one and turn the cash
option and we are setting the cash size
to ten so what is this going to do it's
going to cash the first ten values as we
read the values you know they will be
removed from the cache meaning they will
be read from the memory instead from the
disk so obviously the performance is
going to be much better when we have
exhausted all the values within the
cache and when we request an eleventh
value at that point the next set of ten
values will be cashed again so eleven
twelve until twenty will be read from
the cache and when you request the
twenty first value it's going to cache
again the next ten value so this way by
caching we are going to get better
performance now so far we have used
sequel server commands you know create
sequence command to create the sequence
object we can also use the graphical
user interface to create the sequence
object so we've created sequence object
where are these sequence objects created
now if we create tables you know they
will be created within the tables folder
at the moment we have executed that
create sequence statement within the
context of sample DB database so within
this database those sequence objects
should have been created somewhere
tables will be present in the tables
folder and the sequence object will be
present within the programmability
folder so within programmability we have
sequences folder so within sequences
folder notice that we have our sequence
object now you can also use the sequence
or management studio graphical user
interface to create a sequence object
simply right click on the sequence
folder and select new sequence and
within this window you can see all the
options that you can specify for the
sequence object so your sequence name
let's call it test underscore sequence
and what is the schema name the data
type so notice it is defaulting to begin
now you can also space
by precision you can specify precision
when you choose maybe a decimal datatype
so let's stick to the default begin and
what is the start value let's start
values one increment by one you can
specify min value and maximum value
again those are optional so let's say
min value is 100 and let's start
actually at one minimum value is 1 and
maximum value is 1000 and we want to
cycle the values we want to cash and
cash is going the cache size is going to
be 20 so when we create this it should
create a test sequence object now if you
want to see the definition of the simply
right click on that scrip sequence as
create to new query editor vendor and
look at that create sequence schema name
the sequence object name as the data
type start with a value of 1 increment
by 1 minimum value is 100 maximum value
is 1000 cycle the sequence object and
cash 20 values in our next video we'll
discuss the difference between the
sequence object and identity in sequel
server thank you for listening and have
a great day