//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: Coalesce vs. ISNull SQL


Should I use COALESCE() or ISNULL()?

As with many technology questions involving roughly equivalent choices, it depends. There are a variety of minor differences between COALESCE() and ISNULL(): 
  • COALESCE() is ANSI standard, so that is an advantage for the purists out there. 
     
  • Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output. 
     
  • ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements. COALESCE(), on the other hand, can take multiple inputs: 
     
    SELECT ISNULL(NULL, NULL, 'foo') 
     
    -- yields: 
    Server: Msg 174, Level 15, State 1, Line 1 
    The isnull function requires 2 arguments. 
     
    SELECT COALESCE(NULL, NULL, 'foo') 
     
    -- yields: 
    ---- 
    foo
     
    In order to make this work with ISNULL(), you would have to say: 
     
    SELECT ISNULL(NULL, ISNULL(NULL, 'foo'))
     
  • The result of ISNULL() always takes on the datatype of the first parameter (regardless of whether it is NULL or NOT NULL). COALESCE works more like a CASE expression, which returns a single datatype depending on precendence and accommodating all possible outcomes. For example: 
     
    DECLARE @foo VARCHAR(5) 
    SET @foo = NULL 
    SELECT ISNULL(@foo, '123456789') 
     
    -- yields: 
    -----  
    12345 
     
    SELECT COALESCE(@foo, '123456789') 
     
    -- yields: 
    ---------  
    123456789
     
    This gets more complicated if you start mixing incompatible datatypes, e.g.: 
     
    DECLARE @foo VARCHAR(5), @bar INT 
    SET @foo = 'foo' 
    SET @bar = NULL 
     
    SELECT ISNULL(@foo, @bar) 
    SELECT COALESCE(@foo, @bar) 
     
    -- yields: 
     
    -----  
    foo 
     
    Server: Msg 245, Level 16, State 1, Line 6 
    Syntax error converting the varchar value 'foo' to a column of data type int.
     
  • A relatively scarce difference is the ability to apply constraints to computed columns that use COALESCE() or ISNULL(). SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is not. So: 
     
    CREATE TABLE dbo.Try 

        col1 INT, 
        col2 AS COALESCE(col1, 0)  
            PRIMARY KEY 

    GO 
     
    -- yields: 
    Server: Msg 8111, Level 16, State 2, Line 1 
    Cannot define PRIMARY KEY constraint on nullable column in table 'Try'. 
    Server: Msg 1750, Level 16, State 1, Line 1 
    Could not create constraint. See previous errors.
     
    Whereas the following works successfully: 
     
    CREATE TABLE dbo.Try 

        col1 INT, 
        col2 AS ISNULL(col1, 0)  
            PRIMARY KEY 

    GO
     
  • If you are using COALESCE() and or ISNULL() as a method of allowing optional parameters into your WHERE clause, please see Article #2348 for some useful information (the most common techniques will use a scan, but the article shows methods that will force a more efficient seek). 
     
  • Finally, COALESCE() can generate a less efficient plan in some cases, for example when it is used against a subquery. Take the following example in Pubs and compare the execution plans: 
     
    USE PUBS 
    GO 
     
    SET SHOWPLAN_TEXT ON 
    GO 
     
    SELECT COALESCE 

        (SELECT a2.au_id 
            FROM pubs..authors a2 
            WHERE a2.au_id = a1.au_id), 
        '' 

    FROM authors a1 
     
    SELECT ISNULL 

        (SELECT a2.au_id 
            FROM pubs..authors a2 
            WHERE a2.au_id = a1.au_id), 
        '' 

    FROM authors a1 
    GO 
     
    SET SHOWPLAN_TEXT OFF 
    GO
     
    Notice the extra work that COALESCE() has to do? This may not be a big deal against this tiny table in Pubs, but in a bigger environment this can bring servers to their knees. And no, this hasn't been made any more efficient in SQL Server 2005, you can reproduce the same kind of plan difference in AdventureWorks: 
     
    USE AdventureWorks 
    GO 
     
    SET SHOWPLAN_TEXT ON 
    GO 
     
    SELECT COALESCE 

        (SELECT MAX(Name) 
            FROM Sales.Store s2 
            WHERE s2.name = s1.name),  
        '' 

    FROM Sales.Store s1 
     
    SELECT ISNULL 

        (SELECT MAX(Name) 
            FROM Sales.Store s2 
            WHERE s2.name = s1.name),  
        '' 

    FROM Sales.Store s1 
     
    GO 
    SET SHOWPLAN_TEXT OFF 
    GO

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Where can I get basic info about using stored procedures?

 

 


Created: 1/23/2005 | Last Updated: 6/4/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (233)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...