Returns the first nonnull expression among its arguments.
Returns the data type of expression with the highest data type precedence.
If all expressions are nonnullable, the result is typed as nonnullable.
If all arguments are NULL, COALESCE returns NULL.
At least one of the null values must be a typed NULL.
COALESCE(expression1,...n) is equivalent to the following CASE expression:
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
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:
CREATE TABLE #CheckSumTest
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY