How to get the last generated identity column value in SQL Server - Part 8 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

Oh welcome to presume technologies I am
venket this is part 8 of signal server
in this session will understand how to
retrieve the last generated identity
column value in sequel server we can use
cope underscore identity at identity or
ident underscore current functions to
retrieve the last generated identity
column value in a session we'll also
talk about the differences between these
three approaches of retrieving the
identity column value now if you haven't
watched part 7 of this video series I
strongly encourage you to do so before
continuing with this session now let's
create two different tables so let's say
I have a table called create table test
1 it's going to have an ID column which
is of type integer and I want this
column to be identity column with seed
s1 and increment s1 and I want a value
column which is going to be n where care
of 20 ok let's create another table and
let's call it test - alright let's
create these two tables now execute the
query refresh the tables folder and you
should see both this test 1 and test 2
now let's insert a record into test 1
table insert into test 1 values and if
you remember in test 1 ID column is an
identity column so we don't have to
supply a value for that when we insert a
record into this table so just supply
the value for name I mean the value
column ok
so I'm going to pass x2 this value
column of test 1 table now when we
insert this record what's going to
happen at akkad gets inserted into this
test 1 table now let's retrieve the data
that's there
so select star from test-1 so when you
retrieve the row this ID column is
automatically generated by sequel server
and it got inserted into this table now
let's say I want this generated the last
generated identity column to be
retrieved okay how can I do that
basically one way is to use scope
identity function okay there's a
function called scope identity within
sequel server which we can use
so select scoop identity so when I press
f5 look at that
the last generator ID column value is 1
so I get that let's say let's insert
this record again
now there's another record inserted if I
press f5 look at that
the last generated identity column value
is 2
okay so I have the scope identity
function now it's turns out that there
is another you know global variable that
we can use at at identity
so there is another you know a way to
retrieve the last generated identity
column value so select exit identity
when I execute this let's execute both
of them together so when I execute both
of them together what's happening you
know they give us the same result so
then what's the difference between these
two let's understand that with an
example now there is the reason why we
have created two tables you know you'll
understand that now okay let me create a
trigger on test one table now we haven't
spoken about trigger set which we'll be
talking about in a in a later session in
a great detail but for now understand
that you can create a trigger on a table
for a specific action for example let's
say I have this test one table when I
insert a row into this table I want some
processing to be done automatically so
is that possible yes we can do that how
is it possible using triggers so we
can't create a trigger on a table for a
specific action for example whenever
somebody inserts a row into test one
table I want another row to be
automatically insert I mean another row
to be automatically insert
into test two table by a trigger okay
so let's create a trigger on test one
table so create trigger let's call this
TR for insert on test one table for what
action for insert action as begin and so
your trigger definition goes between
this begin and end statements if you
look at this one we are creating a
trigger called TR for insert on test one
table for insert action so whenever
somebody inserts a row into test one
table whatever you specify here will be
executed okay what I'm going to do here
is I'm going to insert a row into test
two table so insert into test two values
and I'm going to say here maybe a value
of yyy now if you remember in test two
again we have an ID column and value
column and IDE is an identity column
okay
so let's create this trigger and execute
this okay so we have now test 1 and test
2 table and if you remember on test to
test one table we have an insert trigger
as well so let's select and see what's
the current state of the data in test
one record we have to record in test one
table we have two records in test two
table we don't have any records now when
I execute this insert statement what's
going to happen this trigger is also
getting going to get fired
so this trigger now will insert a record
into test two table so now when we
execute this insert query there are two
rows that are going to be inserted one
row into test one table another row by
the trigger into test two table okay and
then once we do that let's take these
two okay
let's enter the value fast so when I
press F I look at that you you get this
message one row effect it twice that's
because one for this insert statement
and one for the trigger itself so
when we execute these two look at the
result okay
scope identity give me number three
whereas at at identity give me number
one now let's execute okay so three is
from test one table one is from test two
table okay so what's the difference
between scope identity and that identity
now the similarity is both of them
returns the last generated value now
scope identity returns the last
generated value in the same session
session is nothing but a connection so
if you look at this I just have one
query editor window open which is
connecting to the secret server so I
just have one session to the sequel
server so in the same session and within
the same scope okay so if you look at
this I just have this insert statement
when I execute this you know the scope
is just the Sinister insert statement so
within the scope okay scope identity has
returned a value which is 3 but whereas
at that identity you know when this
statement got executed there is a
trigger defined on that table and
trigger inserted another row into
another table which in turn generated
another identity column value okay
netted identity returned that value okay
so if you look at the presentation scope
identity returns the last generated
identity value in the same session and
in the same scope whereas added identity
returns the last generated identity
value in the same session but across any
scope all right now let's understand
what do we mean by ident underscore n't
function you know when do we actually
use this and what's the difference
between this and the rest of these two
now if you look at this I don't
understand function you know basically
if you look at the function name itself
it's expecting a table name parameter
meaning you pass the name of a table so
when you pass the name of it
there it's going to give you the last
generated identity column value for that
table okay but across any session and
any scope
okay now session is nothing but a
connection to the sequel server and new
connection okay so let's see now you can
assume that this s1 session so let's say
user one session is this one let's open
another query editor window so this is
like another session okay
now let's say I am inserting a value
into test2 table so insert into test two
values I'm going to say maybe Z Zed Zed
and let's take the same query and go to
user to swender okay so now it's like
this is one session and this is another
session okay now
let's say select ident under scope
current function and look at this this
function expects a table name the
intelligence shows that you need to pass
in the table name parameter here so
let's pass the table name which is
nothing but test - alright so let's
execute this query so insert into test -
ok and we are executing that end user
once connection so execute that let's go
to user - session and execute this one
okay and within user one session I am
running this ident and the scope current
I mean all these statements okay let's
run them and then we'll understand
okay so scope identity at at identity
now look at this both of them are
returning number two because they
operate only within the current session
they don't consider the other sessions
but whereas ident and the scope current
function okay it operates across any
session but for that test two table so
if you look at test two table it should
have got three records now and the last
record was inserted by this user two
session okay which is three the identity
column value is three so your identity
identical current function returns the
last generator the identity column value
for that table across any session and
any scope where as added identity
returns the last generated identity
value within the same session that means
which in the within the same connection
but across any scope but where as coop
identity returns the last generated
identity column value within the same
session and within the same scope okay
most of the times in reality we use coop
underscore identity function to retrieve
the last generator identity value so
that we can return that back to the user
for example if you have an application
where maybe a customer is registering
you know with your company or with it
with your product you know you need to
give him a Prada
tidy okay so as soon as they you know
their register and the record is
inserted into the table we need to
select the the record that has just that
has just been inserted you know the
identity column value for that record
and then return it back to the
application so it can be displayed on
that web page and the user will know
okay this is my customer number okay so
usually in order to retrieve the last
generator identity column we use cope
underscore identity function but there
are other options as well available and
we have seen the differences between
them in this session on this slide you
can find the resources for asp.net and C
sharp interview questions that's it for
today thank you for listening have a
great day