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?