SQL SERVER – SCOPE_IDENTITY (T-SQL) – Identity Column @SQLSERVER
SCOPE_IDENTITY(): This function returns the latest identity value inserted into an identity column in a table. As given in the function name, this function returns the output with in the scope. The scope is a code boundary such as a stored procedure, trigger, function, or batch.
This is function does not have argument. We can use this function in an expression or anywhere in the scope. But, This function should be executed after an insert statement in the scope otherwise it will return a null value.
1. Executing function without insert statement
select SCOPE_IDENTITY() as ‘Current Identity’
I have already created a table with the following structure
CREATE TABLE [dbo].[tblIdentity](
[RowID] [int] IDENTITY(1,1) NOT NULL,
) ON [PRIMARY]