InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 18:25:45 GMT20http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Hugo Kornelis (9/4/2010)[/b][hr][quote][b]frodriguez.im (9/4/2010)[/b][hr]Now how do you differ an empty or blank field from one with no value? I understand them to be the same.[/quote]In a varchar column, the empty value is '' - that is an opening and an ending quote with nothing in between. A great way to represent the middle initial of George Washington, as he had no middle initial.In the same column, no value would be represented by NULL, the special marker to indicate the absence of a value. A great way to represent the middle intial of the 45th president of the United States, as noone currently knows what (if any!) middle initial he or she has.[/quote]Exactly! Hugo for President! :-)Mon, 06 Sep 2010 12:44:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxIn my opinion a more informative comparison would be between NULL and empty string in the same field, e.g.middle name of George Washington. Empty string means "no middle name" (or a data error if there actually is one). NULL means that nothing is said about whether there is a middle name or not and what it's value might be if there is one.That's the general idea but of course nothing stops people from giving their own interpretation to what NULL means in a concrete situation. But that would be a modification of the original NULL concept.Sat, 04 Sep 2010 21:32:21 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]frodriguez.im (9/4/2010)[/b][hr]Now how do you differ an empty or blank field from one with no value? I understand them to be the same.[/quote]In a varchar column, the empty value is '' - that is an opening and an ending quote with nothing in between. A great way to represent the middle initial of George Washington, as he had no middle initial.In the same column, no value would be represented by NULL, the special marker to indicate the absence of a value. A great way to represent the middle intial of the 45th president of the United States, as noone currently knows what (if any!) middle initial he or she has.Sat, 04 Sep 2010 17:14:18 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Alex-668179 (9/3/2010)[/b][hr][quote]I actually agree with most of that except for the "any value" part, a field cannot be any value or any logical operation on it would always return TRUE...[/quote]I don't understand your comment or perhaps you misread my comment or I misread yours. First of all I never said or suggested that a NULL field has "any value"; instead I said that "any value" should be ignored etc. because the field should be treated as not having a value. Second I disagree with the statement that even if I had implied that a NULL field had "any value", then every logical operation would return TRUE. Perhaps you are confusing this with a convention in mathematics which says exactly the opposite: that any statement about the elements of an empty set is TRUE.The reason why I used so many words to say that the field should be treated as not having a value, is that apparently this simple statement is not enough. People still continue to discuss, for example, what NULL means for the value of the field (is it missing, or unknown, or what...) whereas such a discussion is totally inappropriate. Considering the mathematical background of the relational model the only interpretation of NULL that makes any sense to me is the minimalistic interpretation that there is no value. Period. Anything beyond that is too much speculation when it is done in discussing the meaning of NULL in general.[/quote]I misread yours, I had too much to drink last night. If you had implied that the field could be any value then it could be the value that will fulfill the condition tested for and it would return TRUE but since it's not realistic that the field could have any value it's not worth discussing, my mistake.Now how do you differ an empty or blank field from one with no value? I understand them to be the same.Sat, 04 Sep 2010 16:52:54 GMTfrodriguez.imhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote]I actually agree with most of that except for the "any value" part, a field cannot be any value or any logical operation on it would always return TRUE...[/quote]I don't understand your comment or perhaps you misread my comment or I misread yours. First of all I never said or suggested that a NULL field has "any value"; instead I said that "any value" should be ignored etc. because the field should be treated as not having a value. Second I disagree with the statement that even if I had implied that a NULL field had "any value", then every logical operation would return TRUE. Perhaps you are confusing this with a convention in mathematics which says exactly the opposite: that any statement about the elements of an empty set is TRUE.The reason why I used so many words to say that the field should be treated as not having a value, is that apparently this simple statement is not enough. People still continue to discuss, for example, what NULL means for the value of the field (is it missing, or unknown, or what...) whereas such a discussion is totally inappropriate. Considering the mathematical background of the relational model the only interpretation of NULL that makes any sense to me is the minimalistic interpretation that there is no value. Period. Anything beyond that is too much speculation when it is done in discussing the meaning of NULL in general.Fri, 03 Sep 2010 23:22:29 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Hugo Kornelis (8/27/2010)[/b][hr][quote][b]frodriguez.im (8/26/2010)[/b][hr]what is an empty value? AFAIK, NULL is the only way to leave a field empty![/quote]For string columns and variables, the empty string is an often-used synonym for the zero-length string: ''.I guess a varbinary could also be considered empty when the contents are zero-length. Other data types do not support an empty value, as there are no empty values in the various numeric domains, nor in the date, time, or datetime domains. (Maybe xml does support some kind of empty value, though I think you can only do that with untyped xml - but I am far from an expert in the field of xml, so I might be wrong).[/quote]I thought about that when I was writing my comment, my rationale for not mentioning it is that:a) NULL is type independent and these are not so they're sort of not in the same context (in my head at least :)); and b) although they are widely accepted, in essence these are magic values so it's not really empty.ps. you can use an empty string to set an "empty" xml field, you can also use an empty document header or any xml that doesn't really contains any data, either way when you query it and convert it to a string you get an empty string.Fri, 03 Sep 2010 21:41:25 GMTfrodriguez.imhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Alex-668179 (8/26/2010)[/b][hr][quote]AFAIK, NULL is the only way to leave a field empty![/quote]I disagree: setting a field to NULL does not 'leave [or make] a field empty', instead it makes (or should make) any field value, and even the existence of such a value, undefined, inaccessible and irrelevant. That is what I meant before when I wrote before that NULL is a state: just stop thinking of NULL as a value![/quote]I actually agree with most of that except for the "any value" part, a field cannot be any value or any logical operation on it would always return TRUE, replace any value with "an unknown value" and your definition is 100% correct.The use of the term blank or empty field comes from thinking of the stage when the data is inputed, normally somebody will enter the data on a form and any fields that are unknown at the time (inaccessible) or not applicable (irrelevant) will be left [b]blank or empty[/b], when the data makes it to the database one way to express that the field was left blank is to set it to NULL, there may be many cases when normalization or some other method is a better choice, but in many cases using NULL is the best choice.Fri, 03 Sep 2010 21:18:30 GMTfrodriguez.imhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]oscar.leeper (9/2/2010)[/b][hr][quote]if(1=0 and 1=null)[/quote]Does ternary logic have the same rule that allows me to to say that the above is logically equivalent toif 1!=0 or 1!=null[/quote]Well, yes and no.It is true that in ternary (three-valued) logic, just as in boolean (two-valued) logic, [i]A[/i] and [i]B[/i] is completely equivalent to not(not([i]A[/i]) or not([i]B[/i])). But that not only means that if [i]A[/i] and [i]B[/i], is true, not([i]A[/i]) or not([i]B[/i]) is false and if [i]A[/i] and [i]B[/i], is false, not([i]A[/i]) or not([i]B[/i]) is true - it also means that if if [i]A[/i] and [i]B[/i], is unknown, not([i]A[/i]) or not([i]B[/i]) is unknown as well, since not(unknown) is unknown.[quote]If so, I'm back where I started, since in my original example,if 1=0 and 1=nulldid not pass, but its logical equivalent,if 1!=0 or 1!=nulldoes.[/quote]The problem is that you use a simple IF to test things about ternary logic. But a simple IF has only two possible outcomes - one for true, one for both false and unknown. You would have gotten the expected results if you had usedif 1=0 and 1=nullandif not(1<>0 or 1<>null)But you omitted the not in the last one and switched the then and else clauses of the if. Which is fine for the truth values true and false, but not for the truth value unknown. If [i]A[/i] is unknown, then NOT([i]A[/i]) is unknown as well, so that both "IF ([i]A[/i])" and "IF NOT([i]A[/i])" will fall through to the else clause.Thu, 02 Sep 2010 15:25:06 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote]if(1=0 and 1=null)[/quote]Does ternary logic have the same rule that allows me to to say that the above is logically equivalent toif 1!=0 or 1!=nullIf so, I'm back where I started, since in my original example,if 1=0 and 1=nulldid not pass, but its logical equivalent,if 1!=0 or 1!=nulldoes.Thu, 02 Sep 2010 10:58:22 GMToscar.leeperhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxI try to avoid NULLs, as far as is practicable, in my designs. They complicate coding and often result in suboptimal query performance.In suitable cases, I have no problem with so-called 'magic values', if they make logical sense. For example, I accept the idea of using a value like '9999-12-31' for an end date rather than using NULL.One thing I did not like in the article was the repeated use of constructions like [i]WHERE ISNULL(column, magic_value) <> test_value[/i]. Whether you try to handle NULL's inconvenient behaviour with a CASE statement (or the equivalent COALESCE or NULLIF expressions) or ISNULL, the result is the same: a non-SARGable expression.Using an explicit OR seems preferable to me - and you at least give the optimizer a fighting chance at finding an efficient plan. In many cases, rewriting the query as a UNION of the non-NULL and NULL conditions works better still.Anyone who has ever had to write a (correct) query to determine if a NULLable column has changed surely shares my dislike for the things. Sometimes they are unavoidable, but that doesn't mean it's not worth the attempt.PaulThu, 02 Sep 2010 03:37:34 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]oscar.leeper (9/1/2010)[/b][hr]On both my 2k8 and 2k5 installations, I only got the OR behavior to match the article. Is there a setting I'm missing?[/quote]No, you are making a logic error. Your test does not distinguish between Unknown (sorry for being pedantic, but the third value in three-valued logic is [b]NOT[/b] NULL, but [i]Unknown[/i]). According tho the truth tables, the AND test should return False. And it does, but you'd get the same output if it did return Unknown.Try it with this code:[code="sql"]if(1=1 or 1=null)print 'test passed'elseif not(1=1 or 1=null)print 'test didn''t pass'elseprint 'test result unknown'if(1=0 and 1=null)print 'test passed'if not(1=0 and 1=null)print 'test didn''t pass'elseprint 'test result unknown'[/code]Thu, 02 Sep 2010 00:02:48 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote]This next table summarizes the effect of NULLs in AND expressions:AND True False NULL True True False NULL False False False [u]False[/u] NULL NULL False NULL[/quote][quote]This second table summarizes the effect of NULLs in OR expressions:OR True False NULL True True True [u]True[/u] False True False NULL NULL True NULL NULL[/quote]I tested both of these truth tables for the underlined conditions:[b]false AND null[/b]and[b]true OR null[/b]because I wasn't sure about the behavior here. The programmer in me said short circuiting might work, while the ternary logician in me misremembered a NULL result in both of those conditions. On both my 2k8 and 2k5 installations, I only got the OR behavior to match the article. Is there a setting I'm missing? I tried this with ansi_nulls both off and on with the same result.using this SQL:[b]if(1=1 or 1=null) print 'test passed'else print 'test didn''t pass'if(1=0 and 1=null) print 'test passed'else print 'test didn''t pass'[/b]My output is:[b]test passedtest didn't pass[/b]Wed, 01 Sep 2010 16:56:57 GMToscar.leeperhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]frodriguez.im (8/26/2010)[/b][hr]what is an empty value? AFAIK, NULL is the only way to leave a field empty![/quote]For string columns and variables, the empty string is an often-used synonym for the zero-length string: ''.I guess a varbinary could also be considered empty when the contents are zero-length. Other data types do not support an empty value, as there are no empty values in the various numeric domains, nor in the date, time, or datetime domains. (Maybe xml does support some kind of empty value, though I think you can only do that with untyped xml - but I am far from an expert in the field of xml, so I might be wrong).Fri, 27 Aug 2010 00:41:22 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Alex-668179 (8/26/2010)[/b][hr][quote]AFAIK, NULL is the only way to leave a field empty![/quote]I disagree: setting a field to NULL does not 'leave [or make] a field empty', instead it makes (or should make) any field value, and even the existence of such a value, undefined, inaccessible and irrelevant. That is what I meant before when I wrote before that NULL is a state: just stop thinking of NULL as a value![/quote]Z'actly. :-)Thu, 26 Aug 2010 23:36:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote]AFAIK, NULL is the only way to leave a field empty![/quote]I disagree: setting a field to NULL does not 'leave [or make] a field empty', instead it makes (or should make) any field value, and even the existence of such a value, undefined, inaccessible and irrelevant. That is what I meant before when I wrote before that NULL is a state: just stop thinking of NULL as a value!Thu, 26 Aug 2010 22:04:12 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Don Gilman, P.E. (8/25/2010)[/b][hr]It would also be helpful to expand a bit on string manipulations using NULL. While this [b]nice article [/b]is restricted to the DB Engine, I recently had to dig to find out why SSIS was attempting to insert a null string into a varchar column set to disallow NULL. ;-)Turns out that SSIS string concatenation in the Derived Column Data Task will set the column to NULL if any of the input columns is NULL. The work around is using the SSIS ISNULL(<column>,TRUE, FALSE) for each expression... with TRUE being set to an [i]empty string....[/i] and FALSE being the original column.[/quote]The reason for that is the same reason why NULL + 5 = NULL, because NULL is considered unknown in all operations, including string concatenation, if you concatenate an unknown string to a known one the result will logically be unknown or NULL.He did cover that when he said "Also, any non-logical expressions involving NULLs have an unkown, or NULL, result."Thu, 26 Aug 2010 21:25:51 GMTfrodriguez.imhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Adolfo J. Socorro[/b][hr]main cause of confusion, I would say, is thinking that NULL means blank or empty.[/quote][quote][b]Microsoft[/b][hr]A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value.[/quote]If that is true then, what is an empty value? AFAIK, NULL is the only way to leave a field empty!I think it would be more reasonable to say that from the point of view of a logical or arithmetic operation a NULL value is considered unknown since it cannot be resolved to an actual value so it is unknown within the context of the operation and that would make the result of the operation unknown as well, but from the point of view of data storage it is actually a blank or empty field.[quote][b]Socorro[/b][hr]One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value.[/quote]I don't see how that will save you significant amount of time, checking for a NULL is just as easy as checking for any value and it will make your code more readable. Using a magic number is something you would want to do if you didn't had NULL support, it doesn't have any advantages over NULLs (you'll still have to check for the magic value implicitly to find out if it was set), and it has some disadvantages, of the top of my head:1) It can screw up greater than/less than queries (the empty fields may come up on the query when they're not supposed to);2) If you do it on a foreign key then you'll need a dummy record on the referenced table;3) Most front-end data frameworks can handle NULL values without any special handling, for example you can store a NULL value in any nullable data type or use it to set a GUI control directly, a magic value will always require some special handling;4) Any qualified developer should understand the concept of NULLs but may not understand the logic behind your magic value.Thu, 26 Aug 2010 21:16:16 GMTfrodriguez.imhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxIt would also be helpful to expand a bit on string manipulations using NULL. While this [b]nice article [/b]is restricted to the DB Engine, I recently had to dig to find out why SSIS was attempting to insert a null string into a varchar column set to disallow NULL. ;-)Turns out that SSIS string concatenation in the Derived Column Data Task will set the column to NULL if any of the input columns is NULL. The work around is using the SSIS ISNULL(<column>,TRUE, FALSE) for each expression... with TRUE being set to an [i]empty string....[/i] and FALSE being the original column.Wed, 25 Aug 2010 16:38:47 GMTDon Gilman, P.E.http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]tim.stevens (8/24/2010)[/b][hr]This is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold. The concept of NULL is, quite simply, a flawed one and really has not business being a part of the relational model (See E.F. Codd, [i]The Relational Model For Database Management[/i], ISBN 0-201-14192-2). That aside, having a definitive (and informed) strategy for handling these pesky buggers saves hours of hair-pulling.[/quote]You should have mentioned that EF Codd and CJ Date (and others) conducted a very long, very pointed, and very public debate concerning the role of NULL in the relational calculus. As I recall, Date had a column titled "According to Date" in one of the monthly mags (?Database Programming and Design?) from Ziff Davis or Miller Freeman (it has been a long time:-P ).Certainly there is academic interest in various theoretical ideals such as nth Normal Form, NULL-less schemas, etc. Those of us faced with more practical problems need pragamitic solutions.We generally accept that something approaching 3rd Normal is adequate and appropriate for most OLTP situations. Similarly, when properly defined and implemented NULL can yield a nice concise data model that is very efficient and safe. As one example, I have found it very useful in relationship tables that must represent mulitple, changing relationships over time. The "Current" department(s) for a broker is the one whose EndDate is NULL. Previous departments are represented with Start and End date values. This particular implementation permitted multiple, overlapping, and concurrent assignments.Wed, 25 Aug 2010 12:56:22 GMTRay Herringhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxThat's right, John. I tested my scripts only in 2008, so I thought it appropriate to indicate that. I'm sorry if the title gave the impression that there is something new about nulls in 2008.Wed, 25 Aug 2010 09:31:57 GMTAdolfo J. Socorro, Ph.D.http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Kit G (8/25/2010)[/b][hr]5 pages of discussion, but no answer to the question asked a few times. Does SQL 2008 handle NULLs differently from SQL 2005 or SQL 2000? The title implies there is a difference but doesn't discuss it.[/quote]Kit, that's one way of interpreting it. Another is that SQL Server 2008 is the platform that Adolfo is accustomed to using and on which he did his testing. For that reason, he may have preferred not to imply that what he wrote applies to previous versions - even if it actually does.JohnWed, 25 Aug 2010 08:37:53 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx5 pages of discussion, but no answer to the question asked a few times. Does SQL 2008 handle NULLs differently from SQL 2005 or SQL 2000? The title implies there is a difference but doesn't discuss it.Wed, 25 Aug 2010 08:27:55 GMTKit Ghttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxHeh... nah... 5 pages about the philosophy of NULL instead. So much for not hijacking this thread. Tue, 24 Aug 2010 19:07:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxFive pages about NULL and not one mention of using NULLIF to test against a datum...Tue, 24 Aug 2010 17:41:47 GMTJohn Hick-456673http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx"The Semantics of NULL in SQL Server 2008 " -- title is very misleading. Behavior of NULL is same in SQL 7, 2000 and 2005 versions as well. Unless I am missing something, What is so different in 2008 about NULLs?Tue, 24 Aug 2010 14:06:23 GMTkancharla999http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxAnd a well-issued defense at that! I believe that you are referring to the credo of "Normalize until it hurts, denormalize until it stops". I have one client that has a database that was architected for them that was indeed normalized [i]ad infinitum[/i] to where a simple query may entail upwards of 8-10 tables! Clearly, this is an empirical case of "enough is enough".There is, of course, a decent middle ground, in which a certain amount of judiciousness can eliminate spurious nullable columns in which all but a handful are forever left as NULL for the sake of the few exceptions. As with most other disciplines, in database architecture it is in the capacity of making translation from theory into practice that makes theory valuable. While I will admit that NULLs are here to stay, at the very least Adolfo's bold assertion can give us pause to think about using some discretion in applying them.Tue, 24 Aug 2010 11:56:07 GMTtim.stevenshttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]tim.stevens (8/24/2010)[/b][hr]This is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold. The concept of NULL is, quite simply, a flawed one and really has not business being a part of the relational model (See E.F. Codd, [i]The Relational Model For Database Management[/i], ISBN 0-201-14192-2). That aside, having a definitive (and informed) strategy for handling these pesky buggers saves hours of hair-pulling.[/quote]Though it is indeed possible to create a relational database with no NULLs anywhere in the base table, this comes at the expense of a huge increase in the number of tables. And hence in the number of joins required for almost anything.And the gain is way too limited. As soon as you start combining data from these NULL-less tables, you'll get them right back, because you have to use outer joins to keep your data.The academics who want a database without NULLs remind me of one of my first projects in an insurance company. We had been appointed the wrong people to talk to. They told us that every person in the database should have birthday and several other attributes known, so we made them mandatory. Within hours of deploying, we had a visit from people from the actual work floor, showing us application forms where the birthdate and/or other mandatory fields were not filled. Sure, they had to be added before the application could be granted and converted into an actual policy - but the application had to be in the database first, so that the people in the acceptance department could phone the agent about the missing information.(Luckily, the change from NOT NULL to NULL was very easy. The front-end people had a harder job!)It's all fine to say that NULLs are not needed in theory. Theory is not practice. In practice, a business is confronted with missing information all the time, for various reasons. Any application needs some way to deal with that. And until someone finds a better solution than NULL, I'll defend NULL.Tue, 24 Aug 2010 11:34:55 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxMy point exactly. I never advocated the use of "magic values" but rather the use of a well-defined architecture. The judicious normalization of a data model, coupled with appropriate joins, will deliver the same information regarding the presence or absence of data without having to muck about with using NULL AS a magic value of sorts (lifetime subscription, incomplete project, etc.)Tue, 24 Aug 2010 10:35:48 GMTtim.stevenshttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]mtassin (8/24/2010)[/b] ... Just using DateTime Fields I can think of a large number of uses ...Far better to use NULL for what it is than to use a magic value. *shudder*[/quote]It strikes me that you can "hide" the nulls in joins or in bit fields, or you can keep them in the values.(You hide a null in a join by not creating the termination date row, for example.)The object of the architecture should be to make the project work and be clear for maintenance. Within that, I'm not doctrinaire about very many things. But magic values are generally a very poor strategy.Tue, 24 Aug 2010 10:29:49 GMThtroup-1087969http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]tim.stevens (8/24/2010)[/b][hr]This is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold. [/quote]And as wrong as ever. Just using DateTime Fields I can think of a large number of uses (subscription termination date for a life time subscription, project completion date for a project, task completion date for a task on a project,etc ) just off the top of my head.Far better to use NULL for what it is than to use a magic value. *shudder*Tue, 24 Aug 2010 10:20:44 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxThis is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold. The concept of NULL is, quite simply, a flawed one and really has not business being a part of the relational model (See E.F. Codd, [i]The Relational Model For Database Management[/i], ISBN 0-201-14192-2). That aside, having a definitive (and informed) strategy for handling these pesky buggers saves hours of hair-pulling.Tue, 24 Aug 2010 10:11:42 GMTtim.stevenshttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxA very useful article except one small misprint:) [code="other"]select * from tbl where isnull(boolean,[b]1[/b])= 0[/code]I tested, it should be: [code="other"]select * from tbl where isnull(boolean,[b]0[/b])= 0[/code]Tue, 24 Aug 2010 09:57:20 GMTzhoushibin5659-1152161http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxNicely done Adolfo. Very clean article with clear examples and descriptions.Tue, 24 Aug 2010 08:13:54 GMTScott Abrantshttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx"One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for "empty" or "unknown". This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values."This is why I rated this article poorly. This article started out reading as if written as an introduction to a query-writing analyst, and while I thought the introductory paragraph was weak, it at least made sense given who I thought the audience was. But then the article reaches this point where it seems to change to addressing a data modeller. But to save keystrokes, presumably when writing queries? This reeks of cargo-cult programming advice.There is a distinct advantage to using Null rather than a sentinel value or magic value: While the author asserts ternary logic is difficult, I believe this is just a matter of familiarity. In the world at large, there is a consensus to the truth tables of ternary logic, and they can be found easily as a resource (to wit, this article). It's a convention. A sentinel value is the implementer's business rule. There is not necessarily a convention to them; in fact, the meaning may very well be overloaded based on context. This becomes a maintenace nightmare. If the organization's documentation is rock solid and you have an ironclad change management process, do what your organization does; if the organization is prone to cowboy coding, please have the courtesy to those who may have to perform maintenance on your system to never come up with a sentinel value on the fly. I'm tired of digging through views that query other views that rely on some obscurely named field happening to be not negative or less than 99999, etc.Tue, 24 Aug 2010 07:50:51 GMTkeith.gerritsenhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]da-zero (8/24/2010)[/b][hr][quote][b]Carlo Romagnano (8/24/2010)[/b][hr][quote]One way to avoid worrying about NULLs is never to use them, ...declaring columns as not allowing NULLs and designating default values for "empty" or "unknown"...[/quote]...VERY TERRIBLE![/quote]Please elaborate why this is so terrible, that way we can all learn something.[/quote]This is odd advice, at the very best. If you need to deal with empty or unknown, NULL is a sensible way of doing that; much more so that "0 doesn't mean 0" logic. The author seems to include this advice rather off-the-cuff. He doesn't elaborate on the kinds of information architecture that would lead towards preferring default over NULL. Sometimes a record must contain non-NULL values to be usable; defaulting that may allow the logic around missing values to be contained, rather than spread out. But one has to consider the implications - is the default in the normal range? Will there ever be a need to change the default? For example, an expiry date for a subscription. What if you sell a "lifetime" subscription?A fair number of the real Y2K problems came from the use of "in-band" defaults as sentinels - 99 in the date field was once common for "stop here"; in 1998 a lot of people had to clean that up. NULL might be a better choice than any default for a "special" date. Every real date (post 1752) will eventually occur, after all.Tue, 24 Aug 2010 07:35:56 GMThtroup-1087969http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Kit G (8/24/2010)[/b][hr]The title of the article says it covers the handling of NULLs in SQL 2008. Is there any difference between SQL 2005 and SQL 2008?[/quote]As far as I know there is no difference.Tue, 24 Aug 2010 07:30:41 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Carlo Romagnano (8/24/2010)[/b][hr]SQL2008??? What's the difference from the other versions of sqlserver or ANSI SQL?[/quote]I agree - the headline was a "must-read", because it implied a change. I don't see that in the article.Tue, 24 Aug 2010 07:26:03 GMThtroup-1087969http://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxThe title of the article says it covers the handling of NULLs in SQL 2008. Is there any difference between SQL 2005 and SQL 2008?Tue, 24 Aug 2010 07:24:59 GMTKit Ghttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspxBlah Forums flippd out on me for a moment... this was a double post of my other one. :(Tue, 24 Aug 2010 07:03:25 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic973839-2769-1.aspx[quote][b]Jeff Moden (8/24/2010)[/b][hr]Oh jeez.... here we go with bloody semantics again. If data is missing or absent, what value is it, Hugo?[/quote]It's not just semantics. This statement is the root of way too many misunderstandings and misconceptions.I've worked for companies that did business with both natural persons and legal bodies (I hope those are the correct English terms). The differences between the two categories were completely irrrelevant to the business, so their information was kept in a single "Customers" table. But some of the attributes don't aply to both categories. A company has no gender or birthdate, and a natural person does not have a registration number at the chamber of commerce.[quote]The answer is UNKNOWN because you don't know![/quote]Building on the example above. Would you really answer "UNKNOWN" when I ask you to tell me the gender of Microsoft Incorporated, or the chamber of commerce registration number of Jeff Moden?[quote]I can't believe you rated an article as "poor" because of opinionated semantics such as this.[/quote]And I can't believe that you disqualify a definition that is lifted straight from an internationally accepted ISO/ANSI standard as "opinionated semantics".[quote]My recommendation would be that if you good folks really, really want to discuss the alternate definitions of and the philosphy of NULLs, there's a whole separate thread where you can do that (see below)... don't hijack this man's thread for yet another discussion on the semantics and philosphy of nulls.[/quote]I'm not hijacking any thread. I'm pointing out what I think is a flaw in an otherwise reasonable good and valuable article. This thread is the proper place for feedback on this article.Tue, 24 Aug 2010 07:01:57 GMTHugo Kornelis