SQL Server ISNULL function in detail with example

The ISNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression

The following shows the syntax of the ISNULL() function:

ISNULL(expression, replacement)

The ISNULL() function accepts two arguments:

expression In this parameter, we specify the expression in which we need to check NULL values.

replacement is the value to be returned if the expression is NULL. The replacement must be convertible to a value of the type of the expression.

The ISNULL() function returns the replacement if the expression evaluates to NULL. Before returning a value, it implicitly converts the type of replacement to the type of the expression if the types of the two arguments are different.

In case the expression is not NULL, the ISNULL() function returns the value of the expression.

Example:

1) Return the 2nd argument if the first string is NULL value

SELECT ISNULL(NULL, 'SQLServer') result;

Since first string is not NULL value it will return 1st argument

SELECT ISNULL('Rohit techvlog', 'SQLServer') result;

2) ISNULL will support NULL parameter as replacement argument

select ISNULL(NULL,NULL) as IsNUllParam

Output

NULL

3) ISNULL depends on length of first datatype

declare @first as varchar(3)=NULL
declare @sec as varchar(10)='123456'
declare @third as varchar(4)='5689'
 select ISNULL(@first,@sec) as IsNUllParam, ISNULL(@first,@third) as IsNUllParam1

    

Difference between IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in sql server with example

Scope_Identity() returns value generated for the same session and same scope. This is the most common way to find.

@@IDENTITY returns value generated for the same session and  across any scope

IDENT_CURRENT returns value generated for across any session and any scope.

Note: Scope is a module; a Stored Procedure, trigger, function, or batch

Example:

create table Test1(

ID int identity(1,1),

value varchar(30)

)

create table Test2(

ID int identity(1,1),

value varchar(30)

)

insert into Test1

values ('RK'),('MK')

select SCOPE_IDENTITY()

select @@IDENTITY

Now create a trigger in Test1 table

create trigger insertonTest1 on Test1 for Insert

as

Begin

insert into Test2 values ('GK')

End

Now insert new record to Test1 table

insert into Test1

values ('SHG')

Now the below scripts on the same session

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('Test1')

select IDENT_CURRENT('Test2')

Now you can see @@IDENTITY returned the value from different scope. Also IDENT_CURRENT returns as per table name.

Now run the below query on different window means different session.

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('Test1')

select IDENT_CURRENT('Test2')

Since SCOPE_IDENTITY and @@IDENTITY run on different session returned NULL But as the same time IDENT_CURRENT returns the value irrespective of session and scope

You can find other details of Identity columns Below

• Identity Column in SQL Server

• How to find last generated identity column value in SQL Server