Using sp_BlitzIndex - Diagnosed as "Index Hoarder: Addicted to nulls"

Context: MS SQL Server 2012

In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls".

I'm not looking for a debate over NULLs and their place. I know that there are cases where NULLs are perfectly appropriate, and even necessary. The issue here is the feedback provided by sp_BlitzIndex, and the value of enforcing the not NULL constraint.

In most of the tables sp_BlitzIndex "diagnosed" as having numerous columns that allow NULLs, there are not any actual NULL values. We programmatically don't allow them. I just never unchecked the box that says "Allow nulls" to enforce that constraint on the database level.

What benefits does enforcing this constraint provide, and why it is one of the problems that her script identifies?

Thanks!

I think the script in question is sp_BlitzIndex.

The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question:

• Is it valid for those columns to all really contain nulls?

• When tables are created, are people being careful to set the right

properties?

If the answer to both of those questions is yes, then it's totally fine. The check is just there because it's worth thinking about. It's in there mostly just for starting conversations about data integrity and validity, that's all.

Thanks for using the script!

StackExchange Database Administrators Q#83204, answer score: 8