It’s no secret that I hate NULL values.
Well, to be clear, I hate working with NULL values. I’ve written before about why I dislike NULLs. I’ve even had public debates (which I won) about the topic. What I’ve gradually come to realize over time is that it is not the NULL values themselves I dislike. Rather, it is the tools we are given to work with them.
The NULL values are often just an innocent bystander. We don’t blame ourselves or our tools when we have issues with NULLs. We blame the NULLs instead. It’s just easier that way.
While I still reserve a dark corner of my heart for NULL values, I have found I hate something more hideous, more awful, and it must be stopped before it does any more damage.
What could I hate more than NULLs?
Let me show you.
Now, before we go any further, I’m not doing this to complain about any specific product, code, or design. I’m doing this because I do not enjoy having headaches. No one does.
OK, let’s get started.
Configuring SQL Server 2014 Database Mirroring
I built two instances of SQL 2014 running on a single server, and I am going to set up database mirroring of the AdventureWorks2014 database between them. Before I do, let’s take a quick look at the results of this statement:
SELECT *
FROM sys.database_mirroring
Since mirroring is not configured for any database on this instance I expect this view to return no details. As much as I dislike NULLs, I understand how they come into play within a database. And this result set also validates what I find in the BOL entry for sys.database_mirroring:
Got that? Good. Makes total sense to me, too. Now check out the specific description for the mirroring_witness_name column:
So, if I configure mirroring without a witness, then no witness exists, and therefore this column will be NULL. Again, this makes sense to me. All good so far.
OK, let’s move forward. I’ve already got my AdventureWorks2014 database restored (with NORECOVERY, of course) to the second instance and I’m ready to initiate the mirror:
Up comes the Database Properties screen, with the Mirroring page selected, and I will click on the Configure Security button:
I will click past the splash screen to get to the Include Witness Server screen. Here I am going to select “No”:
I’m skipping through the rest of the wizard for this post. You can find plenty of step-by-step guides on configuring mirroring on the intertubz already. I just wanted to show you the part about not choosing a witness, because that’s the critical piece of information for this post.
Verify Mirroring Is Working
After the mirror has been established I can query the sys.database_mirroring system view again and see things are as I expect:
Great! Everything is in order. I’ve mirrored one database and I can see one row has changed from non-NULL values, just as the MSDN entry said it would. All good so far.
Now, being a good DBA, I know it is wise to create an alert to check on the status of the mirror. There are various metrics I might want to be alerted on with regards to database mirroring, but I am going to focus on two specific items:
- I want to know if the state of my mirror is not currently synchronized
- I want to know if the principal and witness servers are not connected
I know, I know…I didn’t configure a witness server. But I want my alert to work for anyone that would, or would not, be using a witness server. And according to the BOL entry, I should be able to do exactly that using the following piece of code:
SELECT *
FROM msdb.sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE (m.mirroring_state_desc IS NOT NULL AND m.mirroring_state_desc <> 'SYNCHRONIZED' )
OR (m.mirroring_witness_name IS NOT NULL AND m.mirroring_witness_state_desc <> 'CONNECTED' )
Now, if I run that code against my SQL 2014 instance I find I get a result back:
Wait, what?
Why would that be the case? Well, since the mirroring_state_desc column shows SYNCHRONIZED, the issue must be with the mirroring_witness_name column. It must be the case that the mirroring_witness_name is not NULL.
Sure enough, this is what I find:
So, you aren’t a NULL value, as the MSDN entry says you should be, otherwise SSMS should show the word NULL with a yellow background (as it did in the earlier screenshots). Oh, and my code is also looking for a non-null mirroring_witness_state_desc value that is anything other than the word CONNECTED (again, as the BOL entry details what I should expect for that column).
So, setting aside that the MSDN entry doesn’t match what we are seeing, the question I must be asking is what is that thing, if not a NULL value?
It’s an empty string.
Yeah, you read that right.
And an empty string is not a NULL value.
NULL is an UNKNOWN, Not Empty!
So, now I have to make some choices.
Do I change my code to look for an empty string instead of a NULL (or perhaps check for either case)? Probably not a bad idea anyway, and something I used to remind myself back in my developer days was to “always expect garbage”.
But this underscores one of my hatreds for working with NULLs. I cannot possibly code for all conceivable non-NULL values that might be used instead of a proper NULL. It’s possible that this view could have returned a space character, or two single quotes, or (*GASP*) it could have returned the word ‘NULL’. I can’t code around all the stupid, forever.
Do I file a Connect item with Microsoft and hope that they find and fix the issue in an upcoming release? That could take a long time and chances are they would likely just update the MSDN entry to reflect what is really happening, leaving me with the fact that I would still need to modify my code.
Another option is to query the msdb.dbo.dbm_mirroring_data table directly to review the results there (this assumes that the Database Mirroring Monitor Job is running successfully on the servers that have mirroring):
SELECT *
FROM [msdb].[dbo].[dbm_monitor_data]
But the data is populated using data from sys.database_mirroring, so that isn’t going to be a perfect solution either. The witness_status column of 0 translates to “UNKNOWN”, and I would want to know when the status is anything other than a value of 1 (which means “CONNECTED”).
Likewise I could just use the db_monitorresults system stored procedure to interpret this table data for me:
EXEC msdb.sys.sp_dbmmonitorresults AdventureWorks2014, 1, 0
I can see that the dbm_monitoring_data table has a 0 for the role on the primary server, which gets translated as a 1 when using the sp_dbmonitorresults system stored procedure, which is helpful since the procedure is documented and the table is not. But the witness_status is still a 0 here.
Perhaps that 0, which means UNKNOWN is a way for Microsoft to tell us that the underlying value is NULL? It is possible that they may have gone out of their way here in an effort to help us. Maybe all we need is to get the MSDN articles (and my code) cleaned up and then we can be on our way.
But it won’t stop my, or anyone’s frustration when working with NULL values, even when they are expected.
Summary
NULLs are a pain to work with, for a variety of reasons, the biggest being the fact that different tools often handle the use of NULLs in different ways. This makes it difficult for end users like me to understand what the data is trying to say.
And I don’t need an empty string to make my life more difficult, either. While coding for junk values is something I would advocate for, I do wish it was not necessary.
Lastly, I should expect that when the documentation says I will have a NULL value then I will have a NULL value, not an empty string. No one should be required to spend time trying to debug the result set of a system (as in, ships with the product) view that shows an empty string when the documentation says it should be NULL.
Our roles as data professionals have enough challenges already.
There are inconsistencies all over the product. Take a look at sys.resource_governor_configuration. When it was introduced, it had only two columns. The second one was correct. It was a bit column that indicated if it was enabled. But the first column returned the ID of the classifier function. Sadly, it returned 0 if there wasn’t a function. It should have been NULL. (The dev told me it was zero in an internal table and he just exposed it “without thinking”). And so on and so on. I keep telling them they need a team of cranky anal old dudes that look at every API that’s designed. It means that when you use the API, you have to special-case the zero value.
Thanks for the comment Greg. Yes, there are some interesting nooks and crannies in the product, and this post wasn’t meant to point out such flaws. I tried to provide a real-world example of how working with NULLs can be frustrating.
Actually there’s a good tool in SQL standard to work with NULLs – the “IS [NOT] DISTINCT FROM” operator, which for unknown reason is still not implemented in SQL Server, no signs for it even in upcoming SQL Server 2016 (at least as of CTP 2.1). Here’s there reference: https://wiki.postgresql.org/wiki/Is_distinct_from
I don’t see the language as being the issue here. The consumer of the data, the average end user, has no concept of what NULLs are and how they may be affecting the data sets they are working with (usually inside of Excel).
At the end of the day we can’t blame the tools, and we can’t blame the people by saying “it’s a training issue”. The data and our tools should be working together in a way to make things simpler. Certain things, like the example above, should be intuitive and just work.
When you find the issue to be a NULL versus an empty string it’s a sad day for everyone.
I’m not a DBA but I do work with data often. The why nulls and triggers were bad caught my attention so I read more. I’m not exactly for or against the 2 so it peaked my curiosity. Unfortunately, to my dismay, most of the arguments are weak, not enough to call something awful. For your null example, the better way to write your WHERE statement should be…
WHERE (ISNULL(m.mirroring_state_desc, ”) ‘SYNCHRONIZED’) OR (ISNULL(m.mirroring_witness_name, ”) ‘CONNECTED’)
I’m not sure if you’re aware of the ISNULL command but it will essentially convert any null value to a value you want, and for good reason too. Had this been a numeric field, you would want to return something like a 0 instead of ”. You may argue that this is exactly why you think nulls are awful but if nulls are not returned for something that’s not there, who’s to say what should be there instead? Let’s take the case of a numeric field. You may say, well return a zero. But what if a zero has a meaning for that field? Let’s say someone created a field that can hold 3 values, 0=Chose No as an answer, 1=Chose Yes as an answer, 2=Chose not to answer, or NULL=did not choose any option. Of course, you may argue now that’s the developer’s issue, whereby they should put a value there to begin with right? Having worked in both worlds of database maintenance and development, I can surely tell you, you will never be able to find consistencies therefore, your argument about null is weak if not outright wrong. Moreover, the statement I wrote is much shorter and easier on the eyes.
As for your argument about triggers, a good developer can easily track down the issue, knowing the data is not what’s expected. If it were up to me, I would go straight to the update/insert statement in the app, extract the command that’s supposed to run, try executing it directly as an SQL statement and quickly isolate the cause, whether it be an application issue or a trigger issue. I’m sorry to say, but I think your “awful” argument is more or less a case of developing your skills.
Thanks for the feedback.
The point about the NULLs above has to do with people not understanding them. The example above is classic: a developer considered an empty string to be the same as a NULL. This drives me crazy.
As for the triggers, I can understand your point about it being a skill level. The job I had at the time was early in my career. We did the exact steps you outlined. Even senior developers…people who wrote the actual triggers…forgot they were there. The tools we have in use today (such as SSMS) do a poor job of highlighting the fact that triggers are in use. We shouldn’t have to hunt for this extra layer of abstraction. For example, when the result message comes back it could say “xx rows modified by this trigger”. That would be good to know, yes?
Do triggers have a valid use case? Absolutely. But that doesn’t mean I have to like them, or advocate their use.
So… I got suckered into reading this by a link that said “triggers are awful… but nulls are far worse”. Good grief, what an incredible amount of whining. As you admitted, NULL values themselves not the problem; it’s people that misuse and misunderstand them (and sometimes the tools) that suck. If it’s your own table, you could just define a constraint to prevent zero-length strings from going into a column. NULL values are useful if not essential for certain situations, such as a date value that has yet to be determined (e.g. “DateTimeStarted” or “DateTimeFinished”) or a column that optionally FKs to a single row in another table (where it’s not many-to-many and you don’t want or need a third associative table).
Here for the same reason. Empty string in particular is an important value, and it should be hammered into everyone’s skull that it is indeed not NULL. Strings over an alphabet being a well ordered set, ” is to strings what 0 is to non-negative numbers, it’s the identity element for string concat, yadda yadda yadda.
This example against NULL has nothing to do with NULL.
In this case, coalesce(rtrim(m.mirroring_state_desc),”) not in (”,’SYNCHRONIZED’) and move along.
Thanks for the comment. This example has to do with the documentation for Microsoft not matching what is in the product. I believe the confusion is that someone on the product team at Microsoft used an empty string when I believe they should have allowed it to be NULL.
Thanks for the comment and the feedback. I wrote the triggers post a long time ago, and I doubt I would write that post with the same voice today. This post is more recent and is part of an ongoing debate I have with others on when and how NULLs are used (or misused).
Hi Thomas, I get that you see the real issue is the people and not the NULL’s. However, I don’t think the argument is sufficient to say NULL’s are a bad idea. For example, you could probably mount a similar argument for zero. Lots of developers get confused and don’t allow for the case with resulting “divide by zero” errors.
I don’t think learning that being careful with division (or being careful with NULL-able columns) is so far beyond the pale that either should be removed.
Where I would agree with you is a strong statement that columns should not be NULL-able as a general rule and only if there is a specific and articulated meaning for the NULL value.
Thanks for the comment!