IS NULL in SQL Server

ISNULL (Transact-SQL)

Replaces NULL with the specified replacement value.

Applies to: 

SQL Server (SQL Server 2008 through current version), Azure SQL Database

Syntax 

ISNULL (check_expression , replacement_value)

Arguments 

Check_expression 

Is the expression to be checked for NULL. check_expression can be of anytype.

Replacement_value 

Is the expression to be returned if check_expression is NULL. 

Replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

Return Types 

Returns the same type as check_expression. 

If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. 

If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

Remarks

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

Case Study: The data type/length of return Value is depends on type/length of check expression(First Argument):

<<http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181924>> 

Hi All,
my requirement is need to form a fixed length string by concatenating 

three columns.

Out of 3 fields if last_name field is null then i need to replace with 

20 spaces.
I tried with below statement:
select @data_var=
column1+
isnull(lastname,replicate(' ',20)) +
column3

The output of this above statement is:
data of column1+one space+data of column2

"it is returning one space but i am expecting 20 spaces"

If i slightly modify the above statement as below with cast statement i am getting correct results:

select @data_var=
column1+
cast(isnull(lastname,replicate(' ',20)) as char(20)) +
column3

the output of this above statement is:
data of column1+20 spaces+data of column2

Please suggest what is happening in first statement(without cast) and 

second(with cast)?

Solution:

This is due to length of the 'lastname' column referred in your first 

script, seems it's length is 1. That's why isnull replaces only one 

space. ISNULL would replace value based on the type\length of the expression you want to replace with.

In your second script you use Cast as char(20), here cast function extends the length(20) no matter what was declared length of column, that's why it adds 20 spaces.