SQL – Difference between COALESCE and ISNULL? [duplicate]

by

Comparing COALESCE and ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  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 NULLability 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 as shown in
    the following example.
> USE tempdb; 
> GO

> -- This statement fails because the PRIMARY KEY cannot accept NULL values
> -- and the nullability of the COALESCE expression for col2 
> -- evaluates to NULL. 

> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0) PRIMARY KEY,  col3 AS ISNULL(col1, 0)  ); 
> 
> -- This statement succeeds because the nullability of the 
> -- ISNULL function evaluates AS NOT NULL.
> 
> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0), 
> col3 AS ISNULL(col1, 0) PRIMARY KEY  );

Validations for ISNULL and
COALESCE are also different. For example, a NULL value for ISNULL is
converted to int whereas for COALESCE, you must provide a data type.
ISNULL takes only 2 parameters whereas COALESCE takes a variable
number of parameters.

Source: BOL

More Related Contents:

  1. How to cast the DateTime to Time
  2. Row Offset in SQL Server
  3. Reset identity seed after deleting records in SQL Server
  4. How to avoid the “divide by zero” error in SQL?
  5. SQL Server recursive self join
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Generate a resultset of incrementing dates in TSQL
  8. How to simulate UNPIVOT in Access?
  9. Find a string by searching all tables in SQL Server
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. How to get script of SQL Server data? [duplicate]
  12. Better techniques for trimming leading zeros in SQL Server?
  13. Common Table Expression, why semicolon?
  14. T-SQL split string based on delimiter
  15. Add unique constraint to combination of two columns
  16. What does a transaction around a single statement do?
  17. SQL Server UNION – What is the default ORDER BY Behaviour
  18. How to get last 7 days data from current datetime to last 7 days in sql server
  19. Joining multiple tables in SQL
  20. sql varchar(max) vs varchar(fix)
  21. Get the character between first 2 special character in SQL
  22. How to check which locks are held on a table
  23. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  24. Inner Joining three tables
  25. SQL take just the numeric values from a varchar
  26. Backup a single table with its data from a database in sql server 2008
  27. What is the meaning of SELECT … FOR XML PATH(‘ ‘),1,1)?
  28. Try_Convert for SQL Server 2008 R2
  29. SQL Server variable scope in a stored procedure
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment