ISNULL()

ISNULL is a TSQL Function which is built into SQL Server. It is NOT a function defined by ANSI-92 – rather it is a feature which Microsoft has elected to include in TSQL in addition to the ANSI SQL standard.

ISNULL() accepts two parameters. The first is evaluated, and if the value is null, the second value is returned (regardless of whether or not it is null). The following queries will return the second parameter in both cases:

SELECT ISNULL(NULL, 1) --Returns 1 SELECT ISNULL(NULL, NULL) --Returns NULL

COALESCE()

COALESCE() is a TSQL function which, like ISNULL, is built into SQL Server. Unlike ISNULL, COALESCE is also a part of the ANSI-92 SQL Standard. Coalesce returns the first non-null expression in a list of expressions. The list can contain two or more items, and each item can be of a different data type. The following are valid examples of COALESCE:

SELECT COALESCE(NULL, 1) --Returns 1   SELECT COALESCE(NULL, 3, NULL, 1) --Returns 3

ISNULL vs. COALESCE

Whenever multiple methods exist for addressing a single problem, the inevitable question is: which method is better? There are a few differences between the two functions which make COALESCE come out on top more often than not:
- COALESCE is ANSI-92 compliant. In the event that you need to port your code to another RDBMS, COALESCE will not require rework.
- COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:
SELECT ISNULL(ISNULL(Col1, Col2), Col3)
- ISNULL constrains the result of a comparison of parameterrs to the datatype of the first value. For example, the following query will produce some often undesirable results using ISNULL, however it will behave as expected with COALESCE:

DECLARE @Field1 char(3), @Field2 char(50) SET @Field2 = 'Some Long String'   SELECT ISNULL(@Field1, @Field2) --Returns 'Som' SELECT COALESCE(@Field1, @Field2) --Returns 'Some Long String'

Note: In other situations, COALESCE will produce unexpected results. COALESCE by nature promotes it’s arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren’t compatable, will of course throw an error). When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime.

For example: SELECT COALESCE(5, GETDATE()) Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime).

ISNULL vs. COALESCE

Whenever multiple methods exist for addressing a single problem, the inevitable question is: which method is better? There are a few differences between the two functions which make COALESCE come out on top more often than not:
- COALESCE is ANSI-92 compliant. In the event that you need to port your code to another RDBMS, COALESCE will not require rework.
- COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:
SELECT ISNULL(ISNULL(Col1, Col2), Col3)
- ISNULL constrains the result of a comparison of parameterrs to the datatype of the first value. For example, the following query will produce some often undesirable results using ISNULL, however it will behave as expected with COALESCE:

DECLARE @Field1 char(3), @Field2 char(50) SET @Field2 = 'Some Long String'   SELECT ISNULL(@Field1, @Field2) --Returns 'Som' SELECT COALESCE(@Field1, @Field2) --Returns 'Some Long String'

Note: In other situations, COALESCE will produce unexpected results. COALESCE by nature promotes it’s arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren’t compatable, will of course throw an error). When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime.

For example: SELECT COALESCE(5, GETDATE()) Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime).