locked
COALESCE confusion RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    hello folks - i am just so confused about COALESCE and need your help. appreciate your time and assistance.

    MSDN says COALESCE is an expression, Oracle documentation says it is a function. Which one is it and what difference does it make?

    https://msdn.microsoft.com/en-us/library/ms190349(v=sql.110).aspx

    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.--- so if Oracle is right about COALESCE being a function, then COALESCE should also evaluate just once. 

    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.--- any idea what the documentation is talking about?


    Saturday, March 7, 2015 4:06 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    What does "have different nullability values."  mean?

    The result of COALESCE and ISNULL can have different data types and nullability.  COALESCE follows the normal data type precedence rules for the result data type whereas ISNULL uses the data type of the first parameter.  COALESCE will return a nullable type but ISNULL may return a NOT NULL type.  The result column of COALESCE in the example below allows null wherease ISNULL does not.

    CREATE TABLE #Test(NullableColumn int NULL);

SELECT ISNULL(NullableColumn, 1) AS TestColumn
INTO #IsnullResult
FROM #Test;

SELECT COALESCE(NullableColumn, 1) AS TestColumn
INTO #CoalesceResult
FROM #Test;

SELECT 
	   COLUMNPROPERTY(OBJECT_ID(N'#IsnullResult', 'U'), N'TestColumn', 'AllowsNull') AS IsnullResultTestColumn
	, COLUMNPROPERTY(OBJECT_ID(N'#CoalesceResult', 'U'), N'TestColumn', 'AllowsNull') AS CoalesceResultTestColumn
GO

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by SnowShine429 Sunday, March 8, 2015 2:33 AM
    Sunday, March 8, 2015 1:26 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    BOL: "The COALESCE expression is a syntactic shortcut for the CASE expression.  That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as  the following CASE expression:

    CASE              

       WHEN (expression1 IS NOT NULL) THEN expression1              

       WHEN (expression2 IS NOT NULL) THEN expression2               

       ...               

       ELSE expressionN              

    END "

    A CASE expression returns a single scalar value.

    BOL describes the differences between ISNULL and COALESCE. Example for one difference:

    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) 
); 
/*
Msg 1711, Level 16, State 1, Line 4
Cannot define PRIMARY KEY constraint on column 'col2' in table '#Demo'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 4
Could not create constraint. See previous errors.
*/
-- This statement succeeds because the nullability of the 
-- ISNULL function evaluates AS NOT NULL.

CREATE TABLE #Demo1 
( 
col1 integer NULL, 
col2 AS COALESCE(col1, 0), 
col3 AS ISNULL(col1, 0) PRIMARY KEY 
);
-- Command(s) completed successfully.

    Reference: https://msdn.microsoft.com/en-us/library/ms190349(v=sql.110).aspx

    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    • Edited by Kalman Toth Saturday, March 7, 2015 5:02 PM
    Saturday, March 7, 2015 4:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    COALESCE is a ANSI-standard SQL function.  According to https://msdn.microsoft.com/en-us/library/ms190286.aspx:

    an expression is "a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function"

    A scalar function like COALESCE can therefore be used as an expression or part of an expression.  The terms function and expression can be used interchangeably depending on the context.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com



    Saturday, March 7, 2015 5:15 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     The terms function and expression can be used interchangeably depending on the context.


    Thanks Dan.  Can you expand on the above?  It is a confusing world where we can interchange terms.

    COALESCE & ISNULL are listed under system functions in SSMS Object Explorer.

    BOL: "Is a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression."

    Link: https://msdn.microsoft.com/en-us/library/ms190286.aspx

    BOL: "Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set."

    Link: https://msdn.microsoft.com/en-us/library/ms191007.aspx

    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    • Edited by Kalman Toth Saturday, March 7, 2015 5:57 PM
    Saturday, March 7, 2015 5:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    COALESCE is a ANSI-standard SQL function.  According to https://msdn.microsoft.com/en-us/library/ms190286.aspx:

    an expression is "a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function"

    A scalar function like COALESCE can therefore be used as an expression or part of an expression.  The terms function and expression can be used interchangeably depending on the context.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com



    thanks Dan, then why does BOL say ISNULL is evaluated once because it is a function while COALESCE is evaluated more than once? What does "have different nullability values."  mean?
    Saturday, March 7, 2015 6:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     The terms function and expression can be used interchangeably depending on the context.

    An expression can pretty much return anything; scalar or structured data, changing

    data types, etc.  

    A function returns a scalar result of a known data type. The functions can be deterministic or non-deterministic.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, March 7, 2015 6:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    >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.

    In the following query the COALESCE would be evaluated 3 times. Sometimes that would give different values.

    WITH CTE AS (SELECT ProductID, IS_NULL=ISNULL(Color, 'N/A'), 
			_COALESCE_=COALESCE(Color, 'N\A') 
             FROM Production.Product)

SELECT ProductID, IS_NULL, _COALESCE_, Col3=IS_NULL, Col4=_COALESCE_ 
FROM CTE
ORDER BY IS_NULL, _COALESCE_
GO

    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Saturday, March 7, 2015 7:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    >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.

    In the following query the COALESCE would be evaluated 3 times. Sometimes that would give different values.

    WITH CTE AS (SELECT ProductID, IS_NULL=ISNULL(Color, 'N/A'), 
			_COALESCE_=COALESCE(Color, 'N\A') 
             FROM Production.Product)

SELECT ProductID, IS_NULL, _COALESCE_, Col3=IS_NULL, Col4=_COALESCE_ 
FROM CTE
ORDER BY IS_NULL, _COALESCE_
GO

    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    i already mentioned that the coalesce expression is evaluated more than once, my question is why is it evaluated 3 times in the above query?
    Saturday, March 7, 2015 8:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    What does "have different nullability values."  mean?

    The result of COALESCE and ISNULL can have different data types and nullability.  COALESCE follows the normal data type precedence rules for the result data type whereas ISNULL uses the data type of the first parameter.  COALESCE will return a nullable type but ISNULL may return a NOT NULL type.  The result column of COALESCE in the example below allows null wherease ISNULL does not.

    CREATE TABLE #Test(NullableColumn int NULL);

SELECT ISNULL(NullableColumn, 1) AS TestColumn
INTO #IsnullResult
FROM #Test;

SELECT COALESCE(NullableColumn, 1) AS TestColumn
INTO #CoalesceResult
FROM #Test;

SELECT 
	   COLUMNPROPERTY(OBJECT_ID(N'#IsnullResult', 'U'), N'TestColumn', 'AllowsNull') AS IsnullResultTestColumn
	, COLUMNPROPERTY(OBJECT_ID(N'#CoalesceResult', 'U'), N'TestColumn', 'AllowsNull') AS CoalesceResultTestColumn
GO

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by SnowShine429 Sunday, March 8, 2015 2:33 AM
    Sunday, March 8, 2015 1:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    thank you Dan, any pointers on why COALESCE is evaluated multiple times?
    Sunday, March 8, 2015 2:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    >i already mentioned that the coalesce expression is evaluated more than once, my question is why is it evaluated 3 times in the above query?

    This is a murky area. The Microsoft SS team decided to implement it that way. The BOL documentation has no explanation on why.

    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Sunday, March 8, 2015 2:53 AM