SQL Server Performance

NULL vs default value

Discussion in 'General Developer Questions' started by serina, Nov 13, 2008.

  1. serina New Member

    Hello,
    i have a table and use a cursor to prove the rows and do some changes (for example take value from row n+1 for row n).
    To perform a faster select query i insert some informations about the row. For example:
    CUSTOMER_ID | DATE | ..... | BUYS_OFTEN | GOOD_FEEDBACK
    12 | 2008-01-01 | ...... | 1 | 1
    12 | 2008-01-01 | ...... | 0 | NULL
    13 | 2008-01-01 | ...... | 1 | 0
    As you see in the row with ID 13 i wrote the 2 possiblities i thought about. There are rows where this information is not needed (the infos are just needed for the first date entry for a specific customer) therefore i have to NULL the fields there or insert a def value. The question is what's better?
    In the first case (NULL) i have to write SELECT WHERE IS NOT NULL and in the second case (def value) i have to write SELECT WHERE IS (1)
    Thx for your opinions.
  2. TommCatt New Member

    When modeling an attribute, one of the questions to ask is, "Is there a value of this datatype that would be meaningful as a default?"Take two date fields: HireDate and BirthDate. The hire date would be the date an employee is hired. In that case, a default set to GetDate() would probably never be far off as the hire date, if omitted from the Insert, would be set to the date the record was created. On the other hand, setting this default for the birthdate would not work at all. So it would make sense to set HireDate as NOT NULL with a default set to GetDate() but Birthdate would have to be set to NULL because only one specific value is valid.(Of course, it would also make sense to set HireDate to NOT NULL without a default -- meaning that a record could never be inserted without a value, but users quickly learn to enter de facto default values on their own to get around that limitation. That is also worth considering.)In your example, the feedback column gives the number of feedback responses from a customer. Is there an integer value that makes sense as a default? Sure. The number 0 works just fine and it gives accurate information as a default. Plus the added benefit in that you never need to watch for a NULL when updating feedback. Your statement would contain "...set GoodFeedback = GoodFeedback + 1..." which would work at all times whenever you receive feedback. Otherwise, if NULL values are allowed, incrementing a NULL always results in a NULL, so you would have to catch that when making the first increment.
  3. FrankKalis Moderator

    Not sure that I agree that 0 makes up a good default value here, but that is probably because I might be misreading something. [:)]
    Why can there be 2 (or even more) rows for the same Customer_ID and the same DATE? What distinguishes one from the other?This is not very clear to me, at least.
    Also, the value in the columns BUYS_OFTEN and GOOD_FEEDBACK suggests that this is used for some interpretation as a boolean value. Now, if so, a default of 0 might skew the results and might lead to wrong results. In that case I definitely would prefer a NULL as default.
    So, I think to answer this question we need to know more details...
  4. serina New Member

    Thanks for your answers guys.
    I can distinguish between the same customer_id and the same date with the time column.
    The thing is that i might get some wrong results if i use aggregate functions over columns with NULL values (for example: avg). therefore i tend to use 0 as default and if the value is higher than 0 i have a valid value for this row.
    An argument might also be that there is a performance advantage when using 0 instead of using NULL.
  5. FrankKalis Moderator

    If you want to make AVG() "null-aware" just do something like
    DECLARE @t TABLE (c1 int)
    INSERT INTO @t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT NULL
    SELECT AVG(ISNULL(c1,0) * 1.) FROM @t
    ALso, I wouldn't be concerned too much over any performance difference between NULL and 0. Both are handled efficiently by the engine.
  6. TommCatt New Member

    If BUYS_OFTEN and GOOD_FEEDBACK are Boolean values of True/False (1/0) then a default is almost invariably a good idea. A new entity will naturally fall into one of the two possible values.However, BUYS_OFTEN and GOOD_FEEDBACK are much too ambiguous of concepts to be described as either completely true or completely false. If BUYS_OFTEN is defined as 10 purchases a month, where does that leave the customer who buys 9 each month?In that case, it would make more sense to make BUYS_OFTEN and GOOD_FEEDBACK a fuzzy unit (FIT), which is a floating point that can range from 0.00 to 1.00. The closer to 0.00 the more False it is and the closer to 1.00 the more True it is. Thus the 9-per-month customer (BUYS_OFTEN = 0.85) would be a lot closer to "a customer who buys often" than the 1-per-month customer (BUYS_OFTEN = 0.05).In any event, a default value (0.00) would still make sense.
  7. FrankKalis Moderator

    [quote user="TommCatt"]If BUYS_OFTEN and GOOD_FEEDBACK are Boolean values of True/False (1/0) then a default is almost invariably a good idea. A new entity will naturally fall into one of the two possible values.
    [/quote]
    Not necessarily. What if I decide not to answer that question for whatever reason? Should this default to TRUE or FALSE? I think neither option is appropriate in this case. NULL might be the "better" choice to avoid skewed results
    This is the "art" of constructing such surveys. It is not that easy as it may seem and needs a lot of thought beforehand. But I think I would also solve it your way. [:)]
  8. TommCatt New Member

    [quote user="FrankKalis"]Not necessarily. What if I decide not to answer that question for whatever reason? Should this default to TRUE or FALSE? I think neither option is appropriate in this case. NULL might be the "better" choice to avoid skewed results[/quote]True, there are some uses for three-valued logic. However, the values in the example do not seem to be responses to a test or questionaire. Until such time a customer has made enough purchases to qualify as one-who-buys-often or generates feedback good enough to qualify as one-who-made-good-feedback then in this case, as in general, a value of False is precisely meaningful.But this may be answerable only by the OP. He has supplied very little in the way of details and all we are left with are assumptions -- never a good situation to be in when making design decisions.
  9. FrankKalis Moderator

    [quote user="TommCatt"]But this may be answerable only by the OP. He has supplied very little in the way of details and all we are left with are assumptions -- never a good situation to be in when making design decisions.
    [/quote]
    True indeed. [:)]
  10. serina New Member

    Thx that was the answer i was looking for :)

Share This Page