> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# IIF ... IS NULL vs. Nz(): Testing a Hypothesis
- URL: https://nolongerset.com/iif-is-null-vs-nz-testing-a-hypothesis/
- Published: 2022-05-14T03:56:24.000Z
- Updated: 2026-05-08T12:57:38.000Z
- Description: Which is better in a WHERE clause: IIf...Is Null or Nz()? Or is there a third option that's better than both of those?
- Author: Mike Wolfe
- Tags: SQL Server, Testing, #Import 2026-05-20 02:59

I made the following claim in an [earlier article](https://nolongerset.com/null-handling-in-ms-access/):

> 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](https://nolongerset.com/null-handling-in-ms-access/#commento-69cb24ab178b7f2f73a814042cfcdc2e97ecc40dd3facb6f0a6b8f82186b19ec) 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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-23.png)

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-24.png)

The function I used to generate the TextValues is one that I use for printing checks, hence the "and NO/100" at the end of each number.

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:

```sql
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 
```

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-25.png)

### MyTable20Indexed

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

```sql
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](https://nolongerset.com/making-sense-of-sqlout-txt/).

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.

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

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

```sql
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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/image-26.png)

*NOTE: I manually added the dividing lines as shown above to the sqlout.txt file after each query run.*

## 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:

```sql
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:

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

This results in the following entry in sqlout.txt:

```sql
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

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

### GOOD

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

---

### Referenced articles

[T-SQL ISNULL() vs. VBA IsNull() vs. Jet IS NULL vs. Access VBA Nz()How do I check thee for NULL? Let me count the ways.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/04/kid-517839_1920.jpg)](https://nolongerset.com/null-handling-in-ms-access/)

*‌Image by [Goodward cc](https://pixabay.com/users/goodward-825733/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=683264) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=683264)*