COALESCE (Transact-SQL)

COALESCE (Transact-SQL)

Returns the first nonnull expression among its arguments.

Return Types

Returns the data type of expression with the highest data type precedence.
If all expressions are nonnullable, the result is typed as nonnullable.
Remarks

If all arguments are NULL, COALESCE returns NULL.
Note Note

At least one of the null values must be a typed NULL.

COALESCE(expression1,...n) is equivalent to the following CASE expression:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. In SQL Server, to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute as in the following statement:
Copy

CREATE TABLE #CheckSumTest
(
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
);