July 6, 2018 at 6:12 am
I like NULL because it enables a limited, trinary condition. Suppose the acceptable values for a column are TRUE or FALSE. Enabling NULL for that column enables three conditions: TRUE, FALSE, and NOT YET REPORTED. Also, for things like mandatory initial profile settings such as the encrypted password where the value must be set, NULL can enable you to know that the password has not yet been set by the user. Using a blank, or zero-length string won't enable this, and I agree with Steve Jones' note that the use of a 'magic value' will become increasingly problematic as the use of reporting and aggregation tools becomes more prevalent.
Chuck Hoffman
------------
I'm not sure why we're here, but I am sure that while
we're here we're supposed to help each other
------------
July 6, 2018 at 6:23 am
allinadazework - Friday, July 6, 2018 1:54 AM...
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!
Agree strongly. The value of NULL is that it is really not a value, and cannot be mistaken for one.
...
-- FORTRAN manual for Xerox Computers --
July 6, 2018 at 6:49 am
Romac - Friday, July 6, 2018 3:44 AMedwardwill - Friday, July 6, 2018 3:38 AMStefan LG - Friday, July 6, 2018 3:34 AMAh 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 = 12324LOL! But "pointless" aliasing is very very useful when using intellisense in (eg) SSMS!
And saves me an awful lot of redundant typing when table names get a little carried away in length!!!
July 6, 2018 at 7:00 am
Bleh.
Nulls are evil. The only place I use them is for optional human-readable comments. Null means "no value". It doesn't even tell you why the value is missing. The context of an optional comment makes the meaning of null obvious in context: nobody could be bothered to add a comment. 🙂
The pro-Null folks like to deride magic numbers as tribal knowledge, and there is a little justification for that. However, like all magic numbers, the cure is to document them. Since nulls are such a fundamental design decision any null-replacement should be A) set in concrete and B) consistent across the all apps in the company and C) beaten into developers heads with a clue stick the instant they join the company.
In effect the purpose and use of null replacements should be as consistent as 0 is in mathematics, at least within the company.
Yes, you do have to code for null replacements BUT if you're clever about it they're much easier to deal with than nulls. Having at least 3 replacements for null (i.e. "to be determined" (the typical use of null), "not applicable" and "verified as unknown" (i.e. the data was lost and there's no way to recover it)) gives "null" a much more useful purpose than null meaning "duh, no idea why it's missing". Of course specific companies may need a wider array of null replacements.
YMMV, of course.
July 6, 2018 at 7:02 am
brad.pears - Friday, July 6, 2018 6:49 AMRomac - Friday, July 6, 2018 3:44 AMedwardwill - Friday, July 6, 2018 3:38 AMStefan LG - Friday, July 6, 2018 3:34 AMAh 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 = 12324LOL! But "pointless" aliasing is very very useful when using intellisense in (eg) SSMS!
And saves me an awful lot of redundant typing when table names get a little carried away in length!!!
See my original SQL - I get to see LOTS of this kind of pointless aliasing. As for "redundant" typing - I'm a touch typist and I know how to use CTRL+C, CTRL+V. The only time I use aliases is when it's mandated (some JOINS etc., or if I'm referencing the same table twice). Neither do I type SELECT Users.GivenName, Users.FamilyName FROM Users when SELECT GivenName, FamilyName FROM Users will suffice.
July 6, 2018 at 7:09 am
The thing about NULL is that it's meaning is contextual, and even different SQL dialects treat NULL differently by default, which can be a problem for those who work in a cross-platform SQL environment. For example, Oracle considers an empty string '' to be functionally equivalent to NULL. It may seem like a small thing, but consider the condition '' = '' evaluates to FALSE in Oracle. I discovered that the hard way several years ago when developing ETL packages that selected from Oracle into SQL Server.
It's kind of like visiting another country where zero still equals zero, but giving someone a "thumbs up" sign has an entirely different meaning.
https://www.slate.com/articles/news_and_politics/explainer/2003/03/what_does_a_thumbs_up_mean_in_iraq.html
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 6, 2018 at 7:37 am
While this is true, any such complaint about NULL really goes double for a magic number.
We KNOW that NULL screws with arithmetic functions, and while different SQLs will handle them somewhat differently, in all systems they are not-a-number. So any properly designed code should be set to skip over NULLs if necessary, regardless of the flavor.
...
-- FORTRAN manual for Xerox Computers --
July 6, 2018 at 8:04 am
I would rather see some fields have a NULL value rather than spaces or -1. This allows me to use ISNULL or COALESCE to assign a different field or value. IMHO this is a lot easier than having to create CASE statements to check for the 'magic' value then decide what to do.SELECT COALESCE(Address1, Address2) AS MailAddress IMHO saying NULLS are evil is just evil. 😀
-- Above it a lot easier to read than
SELECT CASE WHEN Address1 = ''
THEN Address2
ELSE
Address1
END AS MailAddress
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 6, 2018 at 8:19 am
If you do not like NULL, please provide a value that represents all of the space in the universe that is empty.
Whether you like them or not, they simply are (or, rather, are not). And that is the true art of db work: attempting to reflect the world as it is - like it or not.
For example, if you have an optional field on a web form that does not require a user to enter a value, why assign one (i.e. Address2)?
July 6, 2018 at 8:21 am
I love Nulls. I use them extensively in both database design and stored procs/functions.
One of the biggest reasons I use Nulls is for optional columns that need foreign keys. When I inherited the database it was littered with "-1" values or some other meaningless number to indicate the user didn't pick a value. And foreign keys seem to have been a "foreign" concept (pun intended) to my predecessor. Worse than anything, they still had the column as Nullable!
For example, we have teachers in our database, and they must be defined with a Location plus an optional Title and Grade (this is the POSITION table). I would rather use Nulls for Title and Grade instead of added a "fake" value of blank ("") or "n/a" in both of those tables (TITLE and GRADE).
Something new I've started recently is having a unique two column index where only one value is populated. They both have foreign keys and therefore both are Nullable. A specific example is when some piece of data can either be Customer-specific or State-specific but never both. I add a computed column [STATE_OR_CID] AS (isnull([STATE_ID],[CUSTOMER_ID])) that is then added to the unique index. As long as the two IDs have no value in common this works beautifully 🙂
July 6, 2018 at 8:43 am
RLilj33 - Friday, July 6, 2018 8:19 AMIf you do not like NULL, please provide a value that represents all of the space in the universe that is empty.Whether you like them or not, they simply are (or, rather, are not). And that is the true art of db work: attempting to reflect the world as it is - like it or not.
For example, if you have an optional field on a web form that does not require a user to enter a value, why assign one (i.e. Address2)?
Not to be pedantic, but the value that represents empty space in the universe is called "zero point energy". 🙂 It is, in fact, about as far from nothing (null) as it's possible to get... 🙂
July 6, 2018 at 8:48 am
I guess I'm in the minority not liking NULLs. When I develop applications I can't think of any instance that I have allowed NULLs. In place of that I set default values for columns. The applications I develop drive operations on a factory floor and generally are not complicated from a database perspective. I'd love to hear some pros and cons on this.
July 6, 2018 at 8:51 am
I avoid nulls wherever possible, preferring a default value. However, there are situations where NULL is a perfectly valid value in that it means that there is no known value. You cannot feasibly represent a truly 'unknown' value in any other way. If you choose a value to represent unknown (-1 for example), you have a value so it is not truly unknown.
If I *do* use nulls, I will normally only do it on columns the represent data types that are feasibly null such as DateTime, String, etc. I would not normally use a null value for a BIGINT, INT, BIT, etc. since they are ripe for a default value regardless of what language you are using to consume the data.
July 6, 2018 at 8:54 am
guerrerojeffreyr - Friday, July 6, 2018 8:48 AMI guess I'm in the minority not liking NULLs. When I develop applications I can't think of any instance that I have allowed NULLs. In place of that I set default values for columns. The applications I develop drive operations on a factory floor and generally are not complicated from a database perspective. I'd love to hear some pros and cons on this.
I could see your situation, factory production generally has complete control over what values are in the tables, hence there would not be a need (indeed there shouldn't be) an unknown value. A small amount of my work includes data for production machines and every value must be there.
In the broader world, with less controls, unknowns will exist, and we need a way to separate unknown from 'no value' (which in itself could be a legitimate value).
There are things we know
There are things we don't know
There are things we don't know that we don't kow.
Maybe we also need a NULL' null prime
...
-- FORTRAN manual for Xerox Computers --
July 6, 2018 at 8:59 am
My methodology is that NULLs are acceptable if you can justify them. If I see code where a developer is creating a table and they haven't talked to me first for my recommendations, I come back hard on them to justify anything that I see as not mandatory. If they can justify it (and comment it in the code), I allow it. But a lot of times it is more that they just didn't think about when it could be NULL and decided that NULL isn't that bad.
My reasoning for not liking NULLs is it can cause oddities with some things such as math and date calculations.
Sometimes they are recommended or required for an application or may even be intentionally used to filter data or for optional fields in a form.
I also find that sometimes people assume that NULL means the same thing as an empty string or the same thing as 0. Having a value helps people understand what the data is actually telling them.
But I have also been found to use "NULLIF" before when people sometimes use magic numbers and sometimes used null. That makes things extra messy.
And edwardwill, where I work, I would not verify any code that used a single character alias. The alias, if used, must be descriptive and help with readability of the code. If I see an alias of "U", I would ask them to write a better alias.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 16 through 30 (of 143 total)
You must be logged in to reply to this topic. Login to reply