Identity Column in SQL Server - Part 7 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

welcome to presume technologies I am
venket this is part 7 of sequel server
in this session we'll learn everything
about an identity column what is an
identity column how to create an
identity column how to supply explicit
values for an identity column and how to
reset values of an identity column now
to understand this better let's look at
the existing table that have that we
have been working with in all these
parts now we have this table called TBL
person and if you look at this TBL
person table here it has got four
columns ID name email gender ID and age
and if you look at the ID column here
it's a primary key column but it's not
an identity column okay how do I know if
it how do I know it's not an identity
column if you go to the properties of
that particular column in the properties
window you see that identity it is set
to false so it's not an identity column
so if a column is not an identity column
then in sequel server you will have to
supply a value for that for example when
I try to insert a row into this table
and you row into this table how many
columns are there we have 1 2 3 4 5
columns so we have to supply the values
for all the five columns including the
ID column now let's say I want to insert
it insert towards record so the ID is 7
name is Todd email gender ID and age so
if we go ahead and execute this query it
gets inserted fine but but if you look
at the ID column why are we using this
primary key column basically to uniquely
identify each record in this table let's
assume if there are two servers if I
want to uniquely identify them I use
their ID there now I need usually are
some things that users need not have to
provide for example if you have a web
application let's say you are
registering as a customer you don't
provide your customer ID through that
registration form that is something we
want to have automatically calculated by
sequel server ok so under those
circumstances we can actually make use
of the identity column ok so if you mark
it
as an identity column in sequel server
you don't have to supply a value for
that now let us see how to create a
table with an identity column okay since
we already have a table called TBL
person let's try to create another table
called TBL person one maybe so I'm going
to create this table I'm gonna call
maybe we'll call this person ID and
obviously it's an integer now if you
look at this particular column you know
select that column and then drag this
column properties window and you see
that there is something called identity
specification and if you click the plus
sign next to that you see that you know
ease identity okay do you want this
column to be an identity column yes I
want this column to be an identity
column okay and if you look at it as
soon as I have selected yes there are
two other properties here identity seed
and identity increment what are these
basically you are saying you know by
converting this person ID column into an
identity column you're telling sequel
server you want the value for this
column to be automatically computed when
we insert a new row okay now if you want
the value to be automatically computed
you have I mean you can specify how you
want that to be computed for example do
you want that value to start at one or
do you want to start at 100 okay so
that's the seed okay where should I
start usually it's one by default but
you can customize that for example if
you want to start a thousand you can do
so just put seed a thousand there and
how much do you want to increment by
every time when you add a new record do
you want to increment by one two three
four five or ten or hundred okay let's
say for example I want the initial
record to have thousand and every next
record that I insert needs to be
incremented five by five is that
possible absolutely put that increment
as five and every new record you insert
it will be incremented by five but it
doesn't make sense and reality in
reality it usually the seed is 1 and the
increment is 1 but remember you can
customize that
you want to alright so we specified the
information that is required for marking
the column as an identity column and
since I want this to act as a primary
key key I can select primary key
otherwise you can just leave it it's up
to you so I select it as primary key and
then maybe the name of the person let's
say this is n we're care of 50 that's
all I want at this point let's save this
table let's call this TBL person 1 click
OK
which should create that table okay now
if we look at that column let's close
that so if you look at TBL person 1 if I
expand columns refresh so we have this
person ID column which is again set as
primary key and if you go to if we go to
the properties window you should see
this is an identity column true seed at
1 and increment at 1 cool now if I have
to insert a row into this particular
table so select star from TBL person 1
so we don't have any rows in there so if
I want to insert a value into this table
insert into TBL person 1 values look at
this now how many columns have we got
within this table within this table we
have got 2 columns now and we know that
person ID is an identity column so we
don't have to supply a value for that
all I have to do is supply the value for
name column for example let's say John
and when I execute this query what's
going to happen is since we specified
the seed s1 and increment this one it's
going to start at 1 and then put person
IDs 1 for this user John so let's
execute that when we select all the rows
from this table look at that I get this
person ID want and on the other hand
let's say for example if I insert Toms
record execute that select that row we
should have automatically got 2 so it's
incrementing by 1 because we specified
that during marking that column as an
identity column all right now so I have
marked this column as an identity column
now everything is fine I am inserting
new records and you know the person ID
automatically being calculated which is
good okay so at the moment we have
person ID one two and three let's say
for some reason you know maybe John has
left our company or something and this
record got deleted let's delete that
record delete it delete from tbo person
one their person ID is equal to one so
when I delete that row you see that okay
I have Tom and Sarah who is number two
and number three now let's say there is
another person called Todd and I want to
add him to our database when I insert
him and when we select the data back
look at that Todd gets a person idea for
it doesn't use reuse the ID that is not
there you know there is a gap here
number one record is basically not
present and when I insert a new record I
want to reuse that old value is that
possible
absolutely okay there are several ways
to do that let's see first of all how to
supply now let's say for example I'm
gonna insert maybe another record Jane
and I want to issue him person ID of one
is that possible absolutely okay
let's pass in person ID here and see
what's gonna happen when I try to insert
that record remember person ID is an
identity column you don't have to supply
a value for that it will be computed by
sequel server automatically however here
since we want to reuse that person ID of
one with Jane user I am supplying this
value explicitly and let's try to insert
this and see what happens okay
when I press f5 look at that we get an
error okay what does the error say an
explicit value for the identity column
so an ask an explicit value for the
identity column in table TBL person one
can only be specified when a column list
is used and the identity insert is horn
so which means you have to do some
changes here if you want to explicitly
supply a value for an identity column
okay first of all you have to turn the
identity insert on okay so for this
table I want to tell the sequel server
okay I'm going to supply the values for
identity column explicitly and how do
you do that by turning this identity
insert on so let's do that first
okay so to turn the identity insert on
you'll use the set command and what is
that identity insert copied that set
identity insert so for what I need to
set the identity insert for this table
TBL person one on all you have to do is
this so when I execute this query
okay command completed successfully
which means we have turned on the
identity insert for the stabile person 1
and the next thing that we have to do is
when we insert this query look at this
if I I mean we have turned the identity
and sat on but when I execute this query
I get the error again because we have
done half a half of it we need to supply
a column list as well so in the insert
query not just the values I need to
specify what these values are for for
which column I want this values for
person ID and name columns and now when
I execute this query you should see that
Jane has got ID value of 1 okay now
since I have this identity insert turned
on is it possible to drop this value and
just supply name what happens in that
case look at that let's try to you know
I'm not gonna pass a value for person ID
column and let's say for example I have
it I want to insert Martin
so I'm just supplying a value for the
name column okay but keep in mind we
have the identity insert turned on for
this table so when I execute that query
look at this explicit value must be
specified why because you have the
identity and set and on so so if you
don't want to supply the value
explicitly for the identity column then
you have to turn off that because you
fill the gap so let me turn off so that
when I insert the next record it's going
to get the next incremented value for
that column so when I press f5 it's
turned off right now and when we insert
this guy now we are gonna get a new ID
for that person you know now the
sequence continues okay one way to
explicitly supply values for identity
column is basically to turn on the
identity insert cool so we basically
have seen so remember if a column is
marked as an identity column then the
values for this column are automatically
generated okay when we insert a new row
into the table we don't have to supply
values but there might be scenarios
where you know you have deleted some
records and there are gaps generated and
you want to fill those gaps you can
temporarily turn off identity insert and
then fill those gaps I mean you can turn
on identity insert and then fill those
gaps and turn off identity insert all
right now let's say I have deleted all
the rows from this table and I want to
reset the identity insert I mean the
value for identity column is that
possible absolutely let's do one thing
let's delete everything from TBL person
so I'm gonna delete all the rows from
TBL worsen TBL person one table and look
at this when you delete all the rows
from TBL person table don't assume that
it's going to reset the values for
identity column okay so when I select
rows from the table obviously they would
there wouldn't be anything and now when
I insert a new row for Martin and when
we select that row now look at this is
getting the next identity value it's not
resetting that value
okay so if you have deleted all the rows
and you want to reset the identity value
is that possible absolutely we have some
commands called dbcc
database consistency check commands
which we'll be talking about in a great
detail in a later session but understand
at the time being that we have this dbcc
command which we can use to reset the
identity value and then start again at 0
so how do we do that we use that dbcc
check ident check identity basically
dbcc
check identity for this TBL person
reseed that starting at 0 okay starting
at 0 so let us see how to what what this
command does so I'm gonna say dbcc check
identity that's the function and which
for which table we want to do that we
want to do that for TBL person 1 table
and what do we want to do we want to
reseed starting at 0 okay look at this
when I execute this query what's gonna
happen okay so we basically have deleted
everything from this table so there's
nothing there so when we execute this
command look at that you know current
identity value 0 current column value 0
now if I am going to insert this more
row now what's gonna happen the current
identity value is 0 and if you remember
we have said its increment value is 1 so
for this new row Martin it's gonna get 1
and insert that it's gonna start that
again so now when we select the values
from TBL person table you should see
that Martin has got 1 okay so it's also
possible to reset the values for
identity columns if you have deleted all
the rows in the table by using this dbcc
check identity command we'll be talking
about dbcc commands in sequel server in
a later session on the slide you can
find resources for asp.net and C should
have interview questions that's it for
today thank you for listening have a
great day