Allow null on data type money?

  • Some of you will no doubt chuckle over this one.

    I'm self-taught, nobody ever told me and I haven't run across this specific question on the web that I can recall:

    Should data type money allow nulls? Are there valid arguments both pro and con?

    Yes, there is the age-old question regarding how one might interpret a NULL found in any column - does it mean the amount is not known or that the amount is zero (in the case of a numeric type)? You get the drift...

    Other than that, though - are there any practical considerations an old data hound ought to be aware of?

  • I wouldn't allow nulls on data type money for the simple reason that I wouldn't allow the money data type.

    Regarding the real question, I guess it depends more on the attribute than the data type.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I have seen discussion on the matter of even using the money type. In this case we aren't doing any math, so it is in use.

    Perhaps the better question is, should a type (such as decimal) representing money allow nulls?

  • Further on Luis's post, the NULL represents unknown or missing, in the realm of financial transactions that concept is somewhat irrelevant, unknown payment is equivalent of a 0 payment, if the amount paid is unknown it means it isn't a payment. In this case there is no case for NULL.

    😎

  • I wouldn't use nulls to represent a zero value. But I could see a valid option if there's an unknown value that might be defined later. You could also leave those values as zero, but I'm not sure if there's a general consensus on that.

    In other words, nulls in money columns might exist, but they're also rare to find.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I would allow NULLs if it's possible a value won't be known. Sometimes unknown is not the same as zero. For example, I might not know the sales tax rate for a given area, but that doesn't necessarily mean that the sales tax on that item is zero.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Ok, so it does come down to the age-old question...

    Seriously, I marked three solutions, as they all appear to be valid points.

    In our case these dollar amounts are all payroll-related, such as AdditionalWithholding, where zero means the same as NULL. Some are deductions, some are bonuses. And I was mistaken earlier - we are doing math. Just a sum, but it's math. I believe they'd all be fine as NOT NULL with a default of 0.

    Thanks to all.

  • Absolutely. If a NULL means 0, then actually put 0, not NULL. NULL is ambiguous, zero is not. If you compress the row, either value won't take any space anyway.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • This reply has been reported for inappropriate content.

    Hello. I am extremely pleased with the services provided by Astra-trust for my offshore company formation in Hong Kong. The team was not only efficient but also maintained transparency throughout the process. They took care of all the paperwork and legal requirements, allowing me to focus on my business. Their commitment to customer satisfaction sets them apart, and I wholeheartedly recommend site to anyone considering offshore company creation services in Hong Kong.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply