4

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!

Improve this question
0

2 Answers 2

Reset to default
8

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!

Improve this answer
1

I'm not sure what specific problem the script is trying to point out, but no, it is not best practice to unilaterally prevent all columns from allowing nulls.

Allow NULLs when you need them, and don't allow them when you don't. There is no right answer to this; it all depends on how you are using the data and how likely it is that you'll have all of the information for a row at initial insert time.

Some people are afraid of NULL (not talking about Kendra here), but I don't understand it. NULLs certainly have their place.

Possibly a duplicate but in any case worth the read:

Improve this answer
6
  • Thanks. I know that NULLs have their place and we allow them where necessary. Coming from a non-DBA background, I've always allowed NULLs in SQL Server in all columns (except PK), even though many columns do not need them. Is there an advantage to not allowing NULLs in the database, over simply enforcing that rule in the application?
    – Matthew Clemente
    Commented Nov 20, 2014 at 21:10
  • IMHO (and not everyone will agree), constraints should be as close to the data as possible. Why? Data changes don't always go through your application (and it's next to impossible to guarantee that even if you try).
    – Aaron Bertrand
    Commented Nov 20, 2014 at 21:21
  • I agree with you about the superiority of imposing the constraint in the DB. I honestly hadn't really thought about it with regard to NULLs until running this analysis. Is there any performance improvement for adding the constraint, in terms of when the execution plans are put together? Or the benefit is simply a clearer articulation of what the data in the column needs to be?
    – Matthew Clemente
    Commented Nov 20, 2014 at 21:34
  • Haven't tested any impact at that level, so not sure, sorry. Performance certainly wouldn't be my top priority here.
    – Aaron Bertrand
    Commented Nov 20, 2014 at 21:43
  • 2
    @MatthewClemente Allowing columns to be null-able even though you know they do not need to allow them removes a level of self documentation and means other users of your database will end up having to write unnecessarily complex queries to handle the possibility of null. E.g in equality comparisons, or not in, or string concatenation, or addition.
    – Martin Smith
    Commented Nov 21, 2014 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.