Deciding between COALESCE and ISNULL in SQL Server

.

The COALESCE and ISNULL SQL Server statements handle data type precedence differently

COALESCE determines the type of the output based on data type precedence. Since DATETIME has a higher precedence than INT, the following queries both yield DATETIME output, even if that is not what was intended:

DECLARE @int INT, @datetime DATETIME; SELECT COALESCE(@datetime, 0); SELECT COALESCE(@int, CURRENT_TIMESTAMP);

Results:

1900-01-01 00:00:00.000 2012-04-25 14:16:23.360

With ISNULL, the data type is not influenced by data type precedence, but rather by the first item in the list. So swapping ISNULL in for COALESCE on the above query:

DECLARE @int INT, @datetime DATETIME; SELECT ISNULL(@datetime, 0); --SELECT ISNULL(@int, CURRENT_TIMESTAMP);

1900-01-01 00:00:00.000

If you uncomment the second SELECT, the batch terminates with the following error, since you can't implicitly convert a DATETIME to INT:

Msg 257, Level 16, State 3, Line 3 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

While in some cases this can lead to errors, and that is usually a good thing as it allows you to correct the logic, you should also be aware about the potential for silent truncation. I consider this to be data loss without an error or any hint whatsoever that something has gone wrong. For example:

DECLARE @c5 VARCHAR(5); SELECT 'COALESCE', COALESCE(@c5, 'longer name') UNION ALL SELECT 'ISNULL', ISNULL(@c5, 'longer name');

Results:

COALESCE longer name ISNULL longe

This happens because ISNULL takes the data type of the first argument, while COALESCE inspects all of the elements and chooses the best fit (in this case, VARCHAR(11)). You can test this by performing a SELECT INTO:

DECLARE @c5 VARCHAR(5); SELECT c = COALESCE(@c5, 'longer name'), i = ISNULL(@c5, 'longer name') INTO dbo.testing; SELECT name, t = TYPE_NAME(system_type_id), max_length, is_nullable FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.testing');

Results:

name system_type_id max_length is_nullable ---- -------------- ---------- ----------- c varchar 11 1 i varchar 5 0

As an aside, you might notice one other slight difference here: columns created as the result of COALESCE are NULLable, while columns created as a result of ISNULL are not. This is not really an endorsement one way or the other, just an acknowledgement that they behave differently. The biggest impact you'll see from this difference is if you use a computed column and try to create a primary key or other non-null constraint on a computed column defined with COALESCE, you will receive an error:

CREATE TABLE dbo.works ( a INT, b AS ISNULL(a, 15) PRIMARY KEY ); CREATE TABLE dbo.breaks ( a INT, b AS COALESCE(a, 15) PRIMARY KEY );

Result

Msg 1711, Level 16, State 1, Line 1 Cannot define PRIMARY KEY constraint on column 'b' in table 'breaks'. The computed column has to be persisted and not nullable. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.

Using ISNULL, or defining the computed column as PERSISTED, alleviates the problem. Trying again, this works fine:

CREATE TABLE dbo.breaks ( a INT, b AS COALESCE(a, 15) PERSISTED PRIMARY KEY );

Just be aware that if you try to insert more than one row where a is either NULL or 15, you will receive a primary key violation error.

One other slight difference due to data type conversion can be demonstrated with the following query:

DECLARE @c CHAR(10); SELECT 'x' + COALESCE(@c, '') + 'y'; SELECT 'x' + ISNULL(@c, '') + 'y';

Result:

xy x         y

Both columns are converted to VARCHAR(12), but COALESCE ignores the padding implicitly associated with concatenating a CHAR(10), while ISNULL obeys the specification for the first input and converts the empty string to a CHAR(10). refered from MSSQLTIPS.com