The Role of data type precedence for COALESCE and ISNULL Function in SQL Server

Few days ago, I had a discussion regarding key difference between COALESCE and ISNULL function with one of my colleague in my office. Though he knows some of the differences but he doesn’t have any idea about data type precedence in both of these functions. How the data type of the result is determine here. So i thought to write the post regarding that.

In COALESCE data type of the result determines based on the data type precedence. As per the MSDN

Returns the data type of expression with the highest data type precedence.

 Let’s see it practically.

SELECT COALESCE (NULL, 1, GETDATE());

As this function evaluates the arguments in order and returns the first non-nullable value. In the above query first argument is null, so it evaluates second argument which is “1” and is of “Int” data type. But the third argument is “GetDate()” which is of “Datetime” data type and DATETIME data type has a higher precedence than INT. So the output of the above query is in DATETIME i.e. When we execute that query SQL Server will convert “1” into DATETIME.

1900-01-02 00:00:00.000

coalesce_exa1

Let’s see the another query.

SELECT COALESCE (NULL, 'Hello World', 1);

 coalesce_exa2

As you can see in above query that SQL Server will try to convert “Hello World” which is of VARCHAR data type into INT and results in error. Why ? because “INT” has highest data type precedence than “VARCHAR”. You can refer the Data Type Precedence chart from MSDN.

In ISNULL data type of the result determines based on the “first argument”, i.e data type precedence will not influenced the result. As per the MSDN

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

 NOTE: Here check_expression is the first argument and replacement_value is the second argument passed in the ISNULL  function.

Now Let’s see it practically.

DECLARE @var_datetime DATETIME

SELECT ISNULL(@var_datetime, 1)

isnull_exa1

Here variable “@var_datetime” is of type datetime and by default it’s value is NULL. So as the first argument is of type DATETIME which means result type is of datetime. So when you execute query, sql server will convert “1” into datetime and return the result.

Let’s see the another query.

DECLARE @var_int INT

SELECT ISNULL(@var_int, 'Hello World')

isnull_exa2

As you can see that first argument is of type INT. So SQL Server will try to convert “Hello World” which is of VARCHAR into INT and results in error.

That’s it for now. Hope you like this post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s