Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Sic Semper NULL Expand / Collapse
Author
Message
Posted Tuesday, April 10, 2007 11:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
Could we just have a function that will add all non-null values? Something like VSUM where V stays for value?


Regards,
Yelena Varshal

Post #357264
Posted Tuesday, April 10, 2007 11:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404

So... what's wrong with null?  Is it any more correct to have null or a zero length string for a middle name?  I notice the author didn't provide a middle name.  So does he not have one or did he just not provide it.  I could provide a zero length string (blank) or null or you could have a table of "names" and link the person only to those "names" which they have and indicate the position (first, middle, last) but that would be silly.  Normalization is a tool as is the judicious use of null.  When carried to any extreme you become dogmatic.  So, what's correct for an unknown value null or blank?  Maybe it depends on context.

I run a simple business where I only ship items when all the items for the order are available (no back orders).  So ShippedOn is a column in the OrderHeader table.  When I receive an order, it hasn't shipped yet so, what date should be used?  I could create a table of Shipped Orders but that involves a join.  Also, taking that route I'd have to use an outer join to find pending orders.  I could add IsShipped (bit) column to indicate if it's shipped or not but, that's tramp data that could be answered if I just leave the ShippedOn date column.  I could make up a default date (1/1/1900) but that's just as valid as allowing nulls.

Allowing nulls is a tool, just like not allowing nulls.  It depends on context, usage, business rules.  You know -- reality.

 



--Paul Hunter
Post #357273
Posted Tuesday, April 10, 2007 2:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 11:05 AM
Points: 1, Visits: 15

I dig the article Mike.  Way to explore an often-discussed topic more thoroughly than most of those discussions.

I guess it was pretty early on when I decided to embrace the NULL.  It's been easy enough to handle them in the application, and in a facade when I'm not responsible for the application. 

In one case, we got to the end of a project when a last minute requirement popped in requiring certain entities to own other entities.  We added a nullable FK to the entity being owned, referencing the owner.  Since the application was interacting with the database via SP's, we were able get the database ready in an hour or two.  That allowed us to spend the little time we had on the handful of minor changes to the application.

I suppose that anecdote may support the use of SP's more than that of NULL's, but I still dig NULL's

Post #357312
Posted Thursday, April 12, 2007 1:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 12, 2008 10:24 AM
Points: 31, Visits: 24

An important meaning of nulls in the context of nullable columns is “value at present unknown” - the "A mark" null. The "I mark" null is arguably not especially well supported in general, as SQL effectively co-mingles both "types" of nulls. As the author alludes to with regard to tables with nullable columns, it is not unusual to encounter circumstances that may call for taking care with queries to correctly understand and summarize data i.e.(relative to similar tables without nullable columns), and that is perhaps the most obvious cost of NULLs.

A point of some critics of NULLs that appears to be lost here however, is that a DBMS implementation that did not implement NULLs i.e.(supporting a "new" SQL say) would need to handle issues typically handled with NULLs in another (and perhaps better) matter. Another point that may be worth raising is that if NULLs are to be supported for use as either A marks or as I marks, there is at least one product (FirstSql) that arguably supports NULLs "better" than most.




Post #358001
Posted Thursday, April 12, 2007 3:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 4, 2014 10:21 PM
Points: 202, Visits: 307

Thank you for addressing this issue Michael.

Fundamentally, NULL values indicate a fundamental flaw in tuple (noun) definition.  SQL Server's treatment of NULL equality can be unexpected for those who work with other databases.

It’s impossible to avoid NULLs in many cases, specifically because of SQL Server's product position.  It’s often the platform of choice for ETL.  Therefore, many SQL ODS databases can suffer from "NULL" sickness because of a source system's poor table design.

Michael does a good job of explaining the "real life so deal with it" issues.




Post #358050
Posted Thursday, April 12, 2007 6:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:39 AM
Points: 56, Visits: 80

"On the other hand, there are those who would recommend a less dramatic method of dealing with NULL, namely: "learn how to use them properly, and minimize their use where practical."

WOW FINALLY, Someone to voice and understand the true need, and nature of NULL.

I have long argued that there are real world needs for null when the data is truly not known. As an auction company we take in 10's of thousands of items each year, and write detailed condition reports on them. We also create highly detailed attribute lists as part of the condition report.

So as an example if we created defaults of say "No" for an attribute like "Air Conditioning", it could lead to potentially damaging results.

If the condition report writer failed to check the A/C, there would be nothing to alert anyone that the attribute had been missed, as it would look like the asset does not have A/C. If the item does in fact have A/C and the writer simply missed it the end result would be a costly devaluation of the asset. In this case NULL, is valuable, and with 148 distinct attributes for a Truck Tractor or Trailer, as an example, I can only imagine (or rather can't imagine) the nightmare of maintaining and querying this in 6NF. While it is true that this could be handled in the interface by say not allowing the input of a condition report until all the questions are answered, there are distinct and real business reasons why you would not want do that, so null becomes the best solution. The only other course is to set the defaults to "Unanswered" but, then what about numeric fields?

Clearly; when appropriate, defaults and normalization should be used, but I think it was quite elegantly proven in this article that when used thoughtfully and with careful design NULL is a very powerful tool!

Post #358072
Posted Thursday, January 10, 2008 12:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 12, 2014 5:11 PM
Points: 126, Visits: 499
Sorry to open an old thread but I'm fishing for some perspective and hope there are pople out there that can help.

I have several inhouse DB's that long ago someone diecided are better off setting to all not null for columns. The way they were able to do this was by setting everything to not null then defining a default value. For numbers they use -1, strings '', and dates 1/1/1900. From a administration point of view and perfromance point of view it's a freakin night mare for me. To do things like add a column to a 120 gig table as not null with a defualt can take hours of downtime, reindexing data takes a day, and statistics on column and therefore optimizer selection well is skewed to say the least.

The other day I put the foot down they were adding a boolean field of sorts of type Char (1) they had it set to not null and populated 'N' as default. I told them no set it null and when you turn it on then update to 'Y'. What if anything is wrong with that suggestion? What are peoples opinions on utiizing fake data as place holders for nulls?

Thanks for the feedback in advance.

MudLuck



Post #441386
Posted Thursday, January 10, 2008 12:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:39 AM
Points: 56, Visits: 80
This first problem is the char(1) field of 'Y' and 'N'
They should be using a bit field of 1 and 0, its faster, takes less room and has a speicalized indexing routine.

Next, if the natural state of logic for the application is False or True then it is proper to set a default of False or True, however if the true state of the answer is absolutely not known at the time then NULL is the correct choice. It is standard practice to intialize bool's as false. That said, we initialize a number of our bools as NULL because of the following business reason. We have very complex attribute/condition collection forms. There are a lot of Y/N questions on the form that ALL require an answer. The actual state of the answer is not known until the report is completed. We initialize the fields with NULL, so that we can detect an UNANSWERED question. If we initialized the column as either True/False, Y/N. Yes/No, there would be no way to tell the difference between a "missed" answer and a default value. In this way any remaining NULL means that someone needs to get the answer.

It really really depends on the business need and the reason for the default. Merely setting arbitrary default values to eliminate NULLs because someone once heard that NULLs are bad is just as wrong as leaving everything NULL when a default is appropriate.

It is not an exact science which is why the NULL battle still rages on. I hope this helps a bit
Post #441398
Posted Monday, August 3, 2009 6:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 3, 2009 6:43 PM
Points: 1, Visits: 0

This is an interesting post.. thank you for sharing

[color=#F4F8FB]plan solution commission de surendettement[/color][color=#F4F8FB] - commission de surendettement, vous pouvez demander un dossier de surendettement.[/color][color=#F4F8FB]plan solution commission de surendettement[/color]

Post #764503
Posted Thursday, August 27, 2009 8:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 27, 2009 8:06 PM
Points: 1, Visits: 0


Great work .. really informative .. and thanks a lot for sharing

[color=#F4F8FB]calcul credit immobilier courtier taux simulation de france pret[/color][color=#F4F8FB]Une simulation credit immobiler de France a faire un pret.[/color][color=#F4F8FB]calcul credit immobilier courtier taux simulation de france pret[/color]
Post #778854
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse