ISNULL or COALESCE? Sometimes it Really Does Matter

After seeing some poor performance in a query that I couldn't figure out, I ended up reading a lot of discussions about the "ISNULL / COALESCE" debate. Some people feel one is faster than the other, and some people like COALESCE because it's ANSI standard. (And they hold these opinions very strongly.)

I always thought the two were identical (when just two values are involved, of course), but it turns out that they have a difference that can affect performance. The value that ISNULL returns is typed to be same as the type of the first argument. The value that COALESCE returns is typed to be the same as the argument with the highest data type precedence.

Adam Machanic says: "What does this have to do with query performance? Sometimes, when using ISNULL or COALESCE as part of a predicate, a user may end up with a data type mismatch that is not implicitly convertable and which therefore causes a table scan or other less-than-ideal access method to be used."

Data type mismatches can cause table scans? I had totally forgotten about that. Makes sense though: if there's no implicit conversion, you'll have to use a CAST or CONVERT, which means each row will have to be evaluated.

That's what was causing the poor performance I was seeing. I wouldn't advocate a global search-and-replace, but I will be looking more closely at the COALESCE statements I come across.

Convert Any Integer Type to a Hex String

Here's a user-defined function to take any integer from a TINYINT to a BIGINT and return it as a hex number in a VARCHAR like '0x01033FA2':

-----------------------------------------------------------------------------
-- Drop any existing function.
-----------------------------------------------------------------------------

IF OBJECT_ID('dbo.Hexadecimal') IS NOT NULL
BEGIN
   RAISERROR('Dropping user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG
   DROP FUNCTION dbo.Hexadecimal
END


-----------------------------------------------------------------------------
-- Create function to take any integer input and return hex as a string.
-----------------------------------------------------------------------------

RAISERROR('Creating user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG
GO

CREATE FUNCTION dbo.Hexadecimal(@vbInput VARBINARY(255))
RETURNS VARCHAR(18) WITH EXECUTE AS CALLER AS
BEGIN
 DECLARE @sResult   VARCHAR(18)  SET @sResult   = '0x'
 DECLARE @i         INT          SET @i         = 1
 DECLARE @nInputLen INT          SET @nInputLen = DATALENGTH(@vbInput)
 DECLARE @nChar     INT          SET @nChar     = 0
 DECLARE @nHiInt    INT          SET @nHiInt    = 0
 DECLARE @nLoInt    INT          SET @nLoInt    = 0

 WHILE (@i <= @nInputLen)
 BEGIN
  SET @nChar  = CONVERT(INT, SUBSTRING(@vbInput, @i, 1))
  SET @nHiInt = FLOOR(@nChar / 16)
  SET @nLoInt = @nChar - (@nHiInt * 16)

  SET @sResult = @sResult +
       SUBSTRING('0123456789ABCDEF', @nHiInt + 1, 1) +
       SUBSTRING('0123456789ABCDEF', @nLoInt + 1, 1)
  SET @i = @i + 1
 END

 RETURN @sResult
END

GO

RAISERROR('Created  user-defined function Hexadecimal', 10, 1) WITH NOWAIT, LOG


/* Testing.
declare @return char(18)  set @return = ''
declare @bit    bit       set @bit    = 1
declare @tiny   tinyint   set @tiny   = 255
declare @small  smallint  set @small  = 32767
declare @int    int       set @int    = 2147483647
declare @big    bigint    set @big    = 9223372036854775807

print '@bit   maximum is: ' + dbo.Hexadecimal(@bit)
print '@tiny  maximum is: ' + dbo.Hexadecimal(@tiny)
print '@small maximum is: ' + dbo.Hexadecimal(@small)
print '@int   maximum is: ' + dbo.Hexadecimal(@int)
print '@big   maximum is: ' + dbo.Hexadecimal(@big)
-- End testing. */

Show All Indexes And Statistics

A simple script to show all the indexes and statistics.

-- Displays list of all indexes and statistics.
DECLARE @sTableName sysname
SET @sTableName = 'YourTableName'

SELECT OBJECT_NAME(id)                    AS 'Table Name',

       CASE 
            WHEN name IS NULL THEN   '< Heap Table >'
            ELSE                      name
            END                           AS 'I or S Name',
            
       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 1 THEN 'Statistic'
            ELSE
               CASE INDEXPROPERTY(id, name, 'indexid')
               WHEN 0 THEN '-'
               ELSE 'Index'
               END            
            END                           AS 'I or S ?',

       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 1 THEN '-' 
            ELSE
               CASE INDEXPROPERTY(id, name, 'indexid')
               WHEN 0 THEN '-'
               ELSE        CAST(INDEXPROPERTY(id, name, 'indexid') AS sysname)
               END
            END                           AS 'Index ID',
      
       CASE INDEXPROPERTY(id, name, 'IsStatistics')
            WHEN 0 THEN 
                        CASE INDEXPROPERTY(id, name, 'indexid')
                           WHEN 0 THEN '-'
                           WHEN 1 THEN 'Clustered'
                           ELSE        'Non-Clustered'
                        END
            ELSE '-'
            END                           AS 'Index Type',
            
       CASE INDEXPROPERTY(id, name, 'IsUnique')
            WHEN 1 THEN 'Unique'
            ELSE        '-'
            END                           AS 'Unique?'

  FROM sysindexes
 WHERE INDEXPROPERTY(id, name, 'IsHypothetical') = 0
-- AND id = OBJECT_ID(@sTableName)                           -- Restrict by table.
-- AND INDEXPROPERTY(id, name, 'IsStatistics')   = 0         -- 0 = Index, 1 = Statistic
-- AND INDEXPROPERTY(id, name, 'IsUnique')       = 0         -- Restrict by index type.
 ORDER BY 'Table Name',
          'Index Type'

Script to Find Disk Space Occupied by a Table

Before SQL Server 2005 Enterprise Manager made it easy, I used this script to figure out how much disk space a table took up, and the "real" (average, at least) width of a row.

-- Lists the tables in the selected database and the disk space they use. 
-- Also displays the average 'width' of each row in each table. 
SET NOCOUNT ON
DECLARE @sSourceDB AS sysname
SET @sSourceDB = 'Northwind'    -- <====== Set this value.

-- Holds the space used for each table. Column names reflect sp_spaceused().
CREATE TABLE #SpaceUsed 
(
   name       VARCHAR(128),
   rows       VARCHAR(11),
   reserved   VARCHAR(18),
   data       VARCHAR(18),
   index_size VARCHAR(18),
   unused     VARCHAR(18)
)

-- Create and open a cursor on the tables.
DECLARE curTables CURSOR 
    FOR 
 SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_CATALOG = @sSourceDB
    AND TABLE_TYPE = 'BASE TABLE'

OPEN curTables 

-- Iterate the cursor, populating #SpaceUsed for each table from sp_spaceused().
DECLARE @sTableName sysname

FETCH NEXT
   FROM curTables
   INTO @sTableName 

WHILE 0 = @@FETCH_STATUS
BEGIN
   DECLARE @sSql SYSNAME
   SET @sSql = 'EXEC ' + @sSourceDB +
               '..sp_executesql N''INSERT #SpaceUsed EXEC sp_spaceused ' +
               @sTableName + '''' 
   PRINT @sSql
   EXEC(@sSql)

   FETCH NEXT
      FROM curTables
      INTO @sTableName
END

CLOSE curTables
DEALLOCATE curTables 

-- Display results.
SELECT
   name       AS 'Table Name',
   rows       AS 'Row Count',

   CASE CAST(REPLACE(rows, ' KB', '') AS INT)
       WHEN 0  THEN 'N/A'
       ELSE         1024 * CAST(REPLACE(data, ' KB', '') AS INT) /
                           CAST(REPLACE(rows, ' KB', '') AS INT)
   END        AS 'Avg Bytes/Row',

   data       AS 'Data Space +',
   index_size AS 'Index(es) Space +',
   unused     AS 'Unused Space =',
   reserved   AS 'Total Space'
  FROM #SpaceUsed
 ORDER BY CAST(REPLACE(reserved, ' KB', '') AS INT) DESC

-- Done.
DROP TABLE #SpaceUsed