1

My team has a DB table, which is shared across multiple applications. The table has one primary key INT column and most of the columns are nullable boolean using for flags and configuration. Note that not all columns are being used in all applications, but some columns are shared.

The table creator mentioned she chose to go with this approach because the table keeps growing as new requirements are being introduced at later time. She also did not want the database side to decide the default value (since the new columns introduced will be treated as unknown), but rather let each application decides how to handle the null value on the columns it needs. We never have a problem with this approach because the table is always queried by the primary key.

However, our new team member recently reviewed the data table as an outsider view without digging too deep into the reasoning or context of the table and had some concern over having nullable boolean due to potential poor performance. He claimed that the data size will also be larger (in foreseeable future, the table will not grow beyond 50k rows at most) and suggested that the column should be non-nullable and set the default value instead.

Is that concern really the case here?

frostshoxx
  • 538
  • 1
  • 7
  • 24
  • 1
    No, those concerns are not valid in general or in your example. While NULLs should be avoided, they should avoided when a value should not logically ever be NULL; it should be embraced where an unknown/not set value is needed. The size and performance of nullable bits are not really a concern. FYI, this question will almost certainly be closed as opinion-based and it should really be covered on the Software Engineering site instead https://softwareengineering.stackexchange.com/ – UnhandledExcepSean Jul 19 '18 at 17:18
  • 1
    A nullable bool makes sense where it makes sense. You need to realize that going from a not-null bool to a nullable bool can complicate programming - you are moving from 2 states (T/F) to 3 (T/F/null). All your programs now need to be null aware. Having no default values for these columns is going to be frustrating over time, since not all applications will be using all columns. I appA wants to inserts something, and is using only 3 of 10 possible columns, then it needs to specify all 10 columns, and come up with a reasonable default for the non-null columns. And yeah, 50k rows is nothing – Flydog57 Jul 19 '18 at 21:20

1 Answers1

2

(This was too long to be a comment) As someone said "null is evil". It continues saying that null is like a doctor, hope you don't need it, when you need it you really need it. Hope that makes it clear "null" is not something that should be used without thought. As for the performance, it wouldn't have negligible effect because you are talking about a mere 50K rows which is a very small set of data. If you are sure it doesn't cause any problems in your queries then you might let them exist that way but think twice do you really need them to accept null? Wouldn't simply a default value of FALSE (0 - bit) would do? With bit datatype, SQL server internally packs multiple boolean fields into an integer for storage and say 1 bit or 8 bit fields occupy the same space.

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39