FUNCTION IDENT_CURRENT(),IDENT_seed(),IDENT_INCR(),SCOPE_IDENTITY() AND GLOBAL VARIABLE @@IDENTITY

IDENT_CURRENT() returns the last identity value generated for specified table or view. This last identity value generated can be for any session and any scope. In case if you have newly created table without any records or truncated the table then it will show you the identity seed value.

Syntax: IDENT_CURRENT() ( 'table_or_view' )

IDENT_SEED():
Returns the original seed value that was specified when an identity column in a table or a view was created. Changing the current value of an identity column by using DBCC CHECKIDENT() does not change the value returned by this function.

Syntax: IDENT_SEED ( 'table_or_view' )

IDENT_INCR():
Returns the increment value specified during the creation of an identity column in a table or view that has an identity column.

We can test the same using below script:

USE master
GO
CREATE DATABASE IDENT
GO
USE IDENT
GO
create table B1 (id int identity(5,1),name varchar(12))
Go
insert into B1 VALUES ('P1'),('P2'),('P3')
GO
select IDENT_CURRENT('B1')as Identity_current_value_Before_Truncate
GO
TRUNCATE TABLE B1
GO
select IDENT_CURRENT('B1') as Identity_current_value_After_Truncate
select IDENT_seed('b1') as Seed_Value
select IDENT_Incr('b1') as Increment_Value
GO

From output we can see that after inserting records to table, the function IDENT_CURRENT ('B1') returns the last identity value generated and after truncation of table it returns the seed value. Along with this IDENT_SEED and IDENT_INCR returns the seed and increment value for
table B1 i.e. 5 and 1 respectively.

--DROP DATABASE
Use master
GO
DROP DATABASE IDENT
GO

SCOPE_IDENTITY():
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

@@IDENTITY:
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

For SCOPE_IDENTITY() and @@IDENTITY use below script to see the outputs:

USE master
GO
CREATE DATABASE IDENT
GO
USE IDENT
GO
create table B1 (id int identity(5,1),name varchar(12))
insert into B1 VALUES ('P1'),('P2'),('P3'),('P4')
select SCOPE_IDENTITY() as Table_B1
Go
create table D1 (id int identity(4,1),name varchar(12))
insert into D1 VALUES ('P1'),('P2'),('P3')
select SCOPE_IDENTITY()  as Table_D1
go
select @@IDENTITY as Last_generated_ID
GO

--DROP DATABASE
Use master
GO
DROP DATABASE IDENT
GO

Thanks For Reading This Blog!!!