SQL Day 8: Retrieving Identity Column Values

LO1: How to get the last generated identity value

LO2: Difference between SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT(‘Table_Name’)

Prerequisite: SQL Day 7: Identity Column in SQL Server

Let’s say we have two table as ‘Test1’ and ‘Test2’ with the column as ‘ID’ and value.

Create table Test1(
ID int identity(1,1),--(1,1) repersent seed and increment respectively 
Value nvarchar(50)
)
Create table Test2(
ID int identity(1,1),--(1,1) repersent seed and increment respectively 
Value nvarchar(50)
)

Let’s insert the value in Test1 table.

insert into Test1 values ('A')

When we retrieve the value from the Test1 table so you should see the ID column value gets generated automatically because ID column is the Identity column.


As you can see we have inserted a record in Test1 table and ID value get automatically generated as 1.

Now how do we retrieve the last generated value?

To do that we have the built-in SQL function called SCOPE_IDENTITY()

select SCOPE_IDENTITY()


There is an another way to get the identity value, to use the global variable @@IDENTITY

select @@IDENTITY


Difference between SCOPE_IDENTITY() and @@IDENTITY

To understand the difference I have created a Trigger on Test1 table which will insert data into Test2 table whenever we insert data in Test1 table.

Note: Trigger will be covered in the later session. In a short, Trigger can be created on a table for a specific action. For example, whenever any insert is performed on Test1 table, a new row should be inserted in Test2 table.

Syntax:

create trigger TRIGGER_NAME on TABLE_NAME for Insert 
as 
Begin
--Code to execute
End

Example:

create trigger trForInsert on Test1 for Insert 
as 
Begin
 insert into Test2 values ('ZZZ')
End



Now insert a new record in Test1 table, this will also trigger the created trigger on Test1 table and insert a record in Test2 as well.



Now execute the SCOPE_IDENTITY() and @@IDENTITY at the same time.



SCOPE_IDENTITY() return the result as 2 where as @@IDENTITY return the result as 1.
This is happening because SCOPE_IDENTITY() return the Identity value of Same Session (a Session is nothing but a connection to a SQL Server) and the Same Scope and @@IDENTITY return the Identity value of the Same Session and any Scope.

We have another function to get the identity value called IDENT_CURRENT(‘Table_Name’), this will return the identity of the specific table across any session and any scope.


That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading