SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Who Likes NULL?


Who Likes NULL?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: Administrators
Points: 620032 Visits: 21261
Comments posted to this topic are about the item Who Likes NULL?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896123 Visits: 48248
I like NULLs for a lot of things and you can do some great tricks with them in calculations and selection of alternate data. However, and as with all else in SQL Server, I say "It Depends".

Consider the subject of start and end dates. While I agree with you that tribal knowledge base magic numbers aren't usually the way to go, you do know one thing about end dates that haven't happened yet and that is they WILL happen someday in the future and so to avoid all the issues with WHERE @Today < EndDate OR EndDate IS NULL stuff, I just assign '9999" (becomes 9999-01-01 under the covers) to end dates that would otherwise be NULL.

Consider the subject of "Fragmentation". If you follow Kimberly Tripp's wonderful observations in her "The Clustered Index Debate" MCM video about using an ever increasing, narrow, unique, immutable column as the key for your Clustered Indexes and implement them, you might wonder why your Clustered Indexes see very large fragmentation levels and very low percent of page fullness ("Page Density", the other type of fragmentation that's just as and sometimes more deadly than the normal fragmentation people look at). The answer, of course, is INSERTs followed by "expansive" UPDATEs where the rows become larger thanks to the update. This is frequently caused by the "poor man's" audit technique of having a Modified_BY VARCHAR(50) column, for example, that starts its life as a NULL. If you KNOW that nearly every inserted row will be updated and the Modified_BY column will also be updated from NULL to something, then determine the most common largest value and prepad Modified_BY by setting a default for that column of the same number of spaces you just determined. The space will not go to waste because it will later be filled with data and so prevents massive bad page splits, the extra CPU, IO and Log File activity that goes with it all, and prevents you from having to use other than a 100% FILL FACTOR on your large clustered indexes in a vain attempt to prevent fragmentation which also saves a huge amount of disk space, memory, and time doing transaction log backups that don't need to be as large.

I do agree with Greg that a column like "LastSaleDate" should be saved for reports and not stored in data. It's just as bad as having both an EndDate column and a bit flag saying that the (for example) account is no longer active. I also agree with what he cited on the graphs... they should have be 4 nines instead of just 3. ;-) Just kidding on the 4 nines thing. It did make for amateur looking graphs or graphs written by a person who just wanted to get it off their plate. In truth, it probably happened just as Greg would suppose... someone didn't have the tribal knowledge and just did what they knew.

As yet another sidebar, a good QA team or individual would have caught that. If you haven't thanked someone in QA for saving your butt, they're not pushing you hard enough. ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Redmond
Sean Redmond
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4743 Visits: 1069
NULLs are a necessary evil, they are a reflection of the real world. I try to avoid them whenever I can but one cannot avoid them. They can be present whenever one uses outer joins, for example.

Our problem is that the requirements engineers or the developers design the database and very often they don't really care. The database is somewhere where you dump data (as one of them told me). And so we end up with tables whose only non-null field is the primary key.

We have a table that, amongst other things, assigns a specific value to a specific person and it can vary over time. It has also has many, many other columns.
Those who designed the database could have made another table with the current value-person assignation, but no, it was easier to make a big table and create a new row when there was a new assignation. NULLs come in handy for those who want to build a database quickly and don't want to have to think about database design.
Andy Robertson
Andy Robertson
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3679 Visits: 665

I find it very useful to use NULLs in temporary tables during ETL.
Say for example I have a key, value table but the values might come from different sources but I know my full set of keys. I might want to load all the keys I know I should have values for into a temp table and then have multiple statements getting the values from various sources. I can then check for remaining Nulls before I do the final move from the temp table to a table that might not allow nulls and handle the missing values appropriately. it's easier to analyse all the missing values at the end and potentially report back to the user the missing data in one go.
What really grinds my gears is when someone decides that they're not going to allow NULLs but decide that -1 (or some other weird value) will effectively be a NULL value in the table. Perhaps there is some weird reason for doing this now and again, but it annoys me!


edwardwill
edwardwill
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3983 Visits: 550
When designing a table, it is essential to realise that there are real-world circumstances where NULL may be an important and meaningful representation of the data being modelled.
call.copse
call.copse
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14734 Visits: 2463
edwardwill - Friday, July 6, 2018 2:19 AM
When designing a table, it is essential to realise that there are real-world circumstances where NULL may be an important and meaningful representation of the data being modelled.

Exactly, they're not evil or anything to me - the important point for me is - is this the best model of the circumstances we can make? Yes, we may need to allow for them or do something funky when aggregating but if that is the correct model, it's the correct model. In general getting the model correct makes design issues sort themselves out most readily.

Stefan LG
Stefan LG
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1746 Visits: 279
Ah NULLS...just as controversial as spaces vs. tabs!

I especially use it for unknown datetime columns.
Let's say the table has a column for CreateDate and ModifiedDate, then ModifiedDate will be NULL until such time the record is actually modified by somebody.
edwardwill
edwardwill
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3983 Visits: 550
Stefan LG - Friday, July 6, 2018 3:34 AM
Ah NULLS...just as controversial as spaces vs. tabs!

I especially use it for unknown datetime columns.
Let's say the table has a column for CreateDate and ModifiedDate, then ModifiedDate will be NULL until such time the record is actually modified by somebody.

Yeah, but are they as controversial as pointless aliasing:

SELECT * FROM Users U WHERE U.UserId = 12324

Robert McClean
Robert McClean
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 139
edwardwill - Friday, July 6, 2018 2:19 AM
When designing a table, it is essential to realise that there are real-world circumstances where NULL may be an important and meaningful representation of the data being modelled.

Thoroughly agree!

What opinions do people have on, for example, postal address lines? Typically you might have 4 address lines plus city/town, county/state, postal code, country. Most addresses don't use all four lines. If not using NULL, what would you populate the unused address lines with?
Robert McClean
Robert McClean
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 139
edwardwill - Friday, July 6, 2018 3:38 AM
Stefan LG - Friday, July 6, 2018 3:34 AM
Ah NULLS...just as controversial as spaces vs. tabs!

I especially use it for unknown datetime columns.
Let's say the table has a column for CreateDate and ModifiedDate, then ModifiedDate will be NULL until such time the record is actually modified by somebody.

Yeah, but are they as controversial as pointless aliasing:

SELECT * FROM Users U WHERE U.UserId = 12324


LOL! But "pointless" aliasing is very very useful when using intellisense in (eg) SSMS!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search