IIF ... IS NULL vs. Nz(): Testing a Hypothesis

I made the following claim in an earlier article:

When used in a WHERE condition, Nz() requires a full table scan for linked tables, while the equivalent IIf/Is Null combo gets passed through and applied at the source via ODBC

But was this actually true?

Keeping Me Honest

Reader AHeyne challenged my assertion in the comments of that article:

Mike, would you be so kind and double check if your statement When used in a WHERE condition, Nz() requires a full table scan for linked tables, while the equivalent IIf/Is Null combo gets passed through and applied at the source via ODBC is correct? If I run this query (no pass-through query) in a Microsoft Access query SELECT MyField FROM MyTable WHERE IIf(MyField IS NULL, 0, MyField) = 1, where MyTable is a Microsoft SQL Server table, linked via ODBC, the SQL Server Profiler logs this query: SELECT "MyField" ,"dbo"."MyTable"."MyPkField" FROM "dbo"."MyTable". So the SQL Server returns the full table to Access and then Access then seems to filter the records. I tested with the default SQL Server ODBC driver and v17 too.

Had my memory failed me?  Let's find out.

Multiple Scenarios

I'm going to test the following scenarios:

• A small table (20 rows) with no index
• A small table (2o rows) with an index
• A large table (one million rows) with no index
• A large table (one million rows) with an index

Test Table Data Source

In my sandbox database, I have a table named LargeTable with two fields:

• NumericValue: an integer primary key field
• TextValue:  the English string representation of the numeral

‌Here's what the first eleven rows look like.

The values continue in this form all the way up to "One Million and NO/100".  I will use the values in this table to populate the test tables.

Building the Test Tables

MyTable20

Here's the T-SQL to build the small table with no index.  Notice the CASE WHEN statement that I use to introduce some NULL values into the table:

BEGIN TRAN

-- Build the table
CREATE TABLE dbo.MyTable20
(
    MyPKField int not null Identity CONSTRAINT aaaPK_MyTable20 PRIMARY KEY ,
    MyField nvarchar(5) null,
    MyOtherField nvarchar(255) not null
)

-- Populate it
INSERT INTO dbo.MyTable20 (MyField, MyOtherField)
SELECT TOP 20 
  CASE WHEN (LEFT(TextValue,1) = 'F') THEN Null ELSE LEFT(TextValue,5) END
, TextValue
FROM dbo.LargeTable

-- Show the results
SELECT * FROM dbo.MyTable20

COMMIT 

MyTable20Indexed

Uses the same T-SQL as above except for the MyField column definition:

MyField nvarchar(5) null INDEX ix_MyTable20Indexed_MyField,

MyTableLarge

Uses the same T-SQL as "MyTable20", but without the Top 20 clause.  This results in a table with one million records.

MyTableLargeIndexed

Uses the same T-SQL as "MyTable20Indexed", but without the Top 20 clause.

Recording the Results

Rather than use SQL Server Profiler, I went with ODBC Trace SQL mode.

Ultimately, I was only trying to spot any differences between the IIf ... Is Null approach vs. the Nz() approach.  ODBC Trace SQL Mode produces more limited results than SQL Server Profiler, but that also makes it easier to compare results across multiple queries.

First, I ran the following query for all four linked SQL Server tables (only changing the FROM clause).  These ones used my preferred IIf ... IS NULL approach.

SELECT MyField FROM MyTable20 
WHERE IIf(MyField IS NULL, '', MyField) = 'Seven'

Next, I ran the following query against those same tables:

SELECT MyField FROM MyTable20 
WHERE Nz(MyField, '') = 'Seven'

Comparing the Results

I copied and pasted the results from the two sets of query runs into KDiff3, my preferred text file comparison tool.  Those results can be seen in the screenshot below.

As it turns out, they generated identical ODBC commands:

Revisiting My Original Assertion

Based on these tests, the following statement is incorrect:

When used in a WHERE condition, Nz() requires a full table scan for linked tables, while the equivalent IIf/Is Null combo gets passed through and applied at the source via ODBC

It would appear that both approaches result in a full table scan.

You were right to question me, AHeyne!  Thanks for keeping me honest.

...

But that's not the end of the story.

Interpreting the Results

The first line in each sqlout.txt block for the eight queries I ran above looked very much like this:

SQLExecDirect: SELECT "MyField" ,"dbo"."MyTableLargeIndexed"."MyPKField" 
FROM "dbo"."MyTableLargeIndexed"

In other words, the query is pulling all of the records for the table (though, notably, not every field of every record).  It did this whether we used IIf ... Is Null or Nz().

However, there is no need to use either one in the WHERE clause.  We can craft a query with equivalent logic that avoids having to transfer every single table record across the network.  To do that, we split the IIf() statement into two different clauses:

SELECT MyField FROM MyTableLarge WHERE MyField = 'Seven' OR MyField Is Null

This results in the following entry in sqlout.txt:

SQLExecDirect: SELECT "dbo"."MyTableLarge"."MyPKField" 
FROM "dbo"."MyTableLarge" 
WHERE (("MyField" = 'Seven' ) OR ("MyField" IS NULL ) ) 

Thus, only those records that match our criteria get transferred over from SQL Server.  For a large table, this can be a significant performance boost.

Rule of Thumb

Never use Nz() in a WHERE condition.

Instead, replace it with an OR and an IS NULL.

BAD

WHERE Nz(MyField, 'Active') = 'Active'

GOOD

WHERE (MyField = 'Active') OR (MyField IS NULL)

Referenced articles

‌Image by Goodward cc from Pixabay