Are nulls stored in a nonclustered index?

When you index a nullable field, are the rows with nulls stored in the index? It’s easy enough to find out by creating a table with a nullable field, and then creating an index on it:

Here’s the plan:

Yep, SQL Server stores the nulls in the index. That makes sense, really, because sooner or later, you’re going to want to find the rows with nulls, and when you’ve only got a few nulls in a big table, the index can help a lot there.

To learn more about this stuff in action, watch my free 90-minute course, How to Think Like the SQL Server Engine.

Previous Post
What sessions do you want to see at GroupBy next month?
Next Post
In Azure SQL DB, what does “The connection is broken and recovery is not possible” mean?

11 Comments. Leave new

  • SQL Server can also use and index for COUNT operations rather than the table, so it is critical that both have the same number of rows.

    Reply
  • Aimee Kazmierczak
    March 7, 2019 10:22 am

    Your execution plans include the number of rows and time. Where is the setting to turn that on?

    Reply
    • Aimee – that’s in the new SQL Server Management Studio 18. It’s only in preview phase, but it’s publicly available, and preview 7 has been pretty good quality for me.

      Reply
      • Brian Boodman
        March 11, 2019 6:16 am

        If you’re still on SSMS 17, it does includes counts (but not times) in the Live Query Statistics. This may be a decent substitute.

        Reply
  • Alex Friedman
    March 12, 2019 8:27 am

    Interestingly enough, Oracle made the opposite choice and does not store NULLs in the indexes (requiring some ugly tricks with indexing the results of a function if you’d like to find them).

    Reply
  • Wilfred van Dijk
    March 29, 2019 4:03 am

    What about SPARSE columns?

    Reply
  • What if over 90% of a nullable field is eventually filled with NULLs and the slow statement searches using WHERE field IS NULL? (amongst other fields in the non-clustered index)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}