All Type Coding

Search Here

How to get the last generated identity column value in SQL Server.?

Let's suppose we set identity column values  are auto generated. There are several ways in sql server, to retrieve the last identity value that is generated. SCOPE_IDENTITY() built in function to retrieve the last generated column value.
1-Select SCOPE_IDENTITY()
2-Select @@IDENTITY
3-Select IDENT_CURRENT('table_name')
Let's see how to set the Identity .You are seeing in below image on left side of Id column have a yellow color symbol like key it's a primary key symbol means in below image the Id column is a unique column because of primary key and on set the identity of Id column is YES .after selecting the YES the identity Increment and Identity seed is 1 automatically.






































Suppose we have a table Employee1 which have some records.In  below Image I insert a new record That row Id have 10.After inserting the new row we get new last generated id 10.























Now we'll see how to get last generated id of a table.There are many way to get last generated Id of a table Like below.
1-Select SCOPE_IDENTITY()
2-Select @@IDENTITY
3-Select IDENT_CURRENT('table_name')























Thus we can get last generated id of a table.Now We'll learn 
what is diffrence between Scope_Identity,@@Identity and Ident_Current.
The main difference is that between SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT('table_name').The SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. while @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. and the  IDENT_CURRENT('table_name'). function returns  the last identity value generated for a specific table in any session and any scope. 




No comments :

Post a Comment