Month: December 2011

DECLARE @tabs TABLE (  id int identity(1,1) not null

                     , tabname sysname not null );

DECLARE @i int

      , @tabname sysname;
   INSERT INTO @tabs ( tabname )

        SELECT name

          FROM sysobjects

         WHERE type = ‘U’;
SELECT @i = max(id) FROM @tabs;
WHILE @i > 0

 BEGIN

   SELECT @tabname = tabname FROM @tabs WHERE id = @i

   PRINT ‘UPDATE STATISTICS ‘ + @tabname + ‘ WITH ALL’

   SET @i = @i – 1

 END;
GO

1. Data type determination of the resulting expression – ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence

2. The NULLability of result expression can be different for ISNULL and COALESCE. ISNULL return value is considered NOT NULLable if the return value is a non-nullable one (in the case when the argument that is returns is based on a non-null column or constant). Whereas COALESCE is not. 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 and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.

3. Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex:

ISNULL(NULL, NULL) — is int

COALESCE(NULL, NULL) — Will throw an error

COALESCE(CAST(NULL as int), NULL) — it valid and returns int

4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters. You have to have nested ISNULL to get the same effect as COALESCE.

5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL built-in function.

6. You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and some queries with COALESCE be sub-optimal.