Thursday, January 23, 2014

COALESCE (Transact-SQL)

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL (Avalia os argumentos ordenadamente e retorna o valor atual da primeira expressão que, inicialmente, não avalia NULL).

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
(A função ISNULL e COALESCE a expressão tem um propósito semelhante, mas podem se comportar de forma diferente)
  1. Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.
  2. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
  3. The NULL ability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed.
sample code...

SELECT 
CAST ( COALESCE ( hour_rate * 40 * 52, base_sal, comis * number_sales )   AS money)  AS 'Total' 
FROM [table]
ORDER BY 'Total';






No comments: