2 questions... first x > '' vs x <> ''

  • I am struggling to work with an old oracle developer who does some mighty strange things and I was wondering if one or two of you gurus can clear this up.

    first, when she is trying to evaluate an empty string in a where statement she will do this..

    (exerp).. and s.column > ''

    and I think it should go like this

    and s.column <> ''

    can you tell me which is better or 'right'? of course I think I'm right, but I always do.

    second, since she was an oracle developer, she doesnt believe in nulls. I love them and use them... and she tries to strip them all out. she claims that nulls make writing queries harder, and we dont want any outward facing data to say 'null'. I contend that they are useful and necessary when you dont know a value...

    Can you provide me with a link to a paper that proves how valuable nulls are... and how t-sql is better than oracle? I'm looking for gains in indexing space and speed with null(I hope). Please help me out if you can.

    thanks for your help

    c

  • Either way of checking for a non-empty string works. s.column > '' works because any non-empty string will be greater than ''

  • Thanks for that. One less thing to worry about.

    thank you

  • Here is where I must concur with Celko. Nulls should be avoided except when a null value has real meaning to the business logic. (ex: CancellationDate, or MiddleName )

    The reasons why are many and should be self evident to those with experience in designing large and/or high performance transactional systems, Celko mentioned one important one from an application standpoint.

    The probability of survival is inversely proportional to the angle of arrival.

  • s.column <> ''

    What do you want to happen if the column is null?

    I agree with you about nulls being useful but you have to think about the users of a database.

    A lot of people assume a 2 valued logic system so if business people are going to be given ad hoc access to the database or you want non-database developers to access it (e.g. report writers) then I would try to stay away from nulls. That almost certainly means they should be avoided in datamarts.

    Database developers should be expceted to know or learn how to handle nulls.

    If all access is via stored procedures and those are written or reviewed by database developers then you have a lot more flexibility and life is a lot easier.


    Cursors never.
    DTS - only when needed and never to control.

  • Thanks to all.

    1. I will only use nulls where the value is unknown. middle name is a perfect example.

    2. I use .net for a front end for sql, so I'm not overly concerned about handling nulls... but if there is a caveat with .net and null, I'd like to know.

    3. in the case of <> ''... it will not have null fields because the developer doesnt like nulls. I'm trying to build a case to start using them.

  • The cases for and against "null" in databases is argued endlessly.

    Ask the dev how he/she wants to handle known-unknowns without using nulls. If the answer is something that can be reasonably implemented, use it. If the answer is a blank stare, a brush-off, or requires solving the unified field problem, use nulls.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • WHERE s.column >'' will be marginally more efficient than WHERE s.column<>''

    Nulls are sometime necessary, for example if I have a DrivingConvictions table in a car insurance database I might have

    DriverID

    OffenceCode

    PenaltyPoints

    IllegalSubstanceReading

    If I am done for speeding (some hope in my car) then Illegalsubstancereading could legitemately be NULL. If I am breath tested then theoretically I can get a zero reading but I believe human biology prevents it.

    Recording zero would imply that there was a breath/drug test carried out.

    I don't like nulls because I have to code either in the app or the DB to allow for comparisons on them.

    I believe .NET also requires you to use a DBNull function.

    On big systems you want your tables as efficient as possible and NULL values require a setting in the 192 byte record header.

  • David.Poole (11/11/2010)


    WHERE s.column >'' will be marginally more efficient than WHERE s.column<>''

    Nulls are sometime necessary, for example if I have a DrivingConvictions table in a car insurance database I might have

    DriverID

    OffenceCode

    PenaltyPoints

    IllegalSubstanceReading

    If I am done for speeding (some hope in my car) then Illegalsubstancereading could legitemately be NULL. If I am breath tested then theoretically I can get a zero reading but I believe human biology prevents it.

    Recording zero would imply that there was a breath/drug test carried out.

    I don't like nulls because I have to code either in the app or the DB to allow for comparisons on them.

    I believe .NET also requires you to use a DBNull function.

    On big systems you want your tables as efficient as possible and NULL values require a setting in the 192 byte record header.

    And that's why there need to be two different versions of null. One for "we don't know" and another for "we don't care". (Okay, really for "don't know" vs "known no value", but the other is easier to remember.) Till then, null = "unknown value or no value".

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • 2. I use .net for a front end for sql, so I'm not overly concerned about handling nulls... but if there is a caveat with .net and null, I'd like to know.

    In your .NET you'll need to get into the habit of testing for NULLs before attempting to reference any value from a, say, DataRow cell. If you attempt to reference NULL as a value in .NET, an error will be thrown.

    Pretty easy to work around.

    For example, when binding, instead of:

    MyControl.Text = Cstr(MyDataRow("Column1"))

    You would say:

    If Not IsDbNull(MyDataRow("Column1")) Then

    MyControl.Text = Cstr(MyDataRow("Column1"))

    End if

    Or, as suggested by nigelrivett, make sure all data can only be returned to the DAL via Stored Procedures, and make sure all values returned by the Stored Procedures are COALESCED.

    If you don't have that level of control, you'll need to trap in .NET, either in the Presenattion layer as described above, or you could do a NULL scrub in the DAL before turning data over to the Presentation layer.

  • If you have to build application code to scrub nulls then surely it would have been better not to have them in the first place. Surely allowing nulls just adds one more thing to have to build tests for?

    If you have a DAL layer then clean on the way in to stop having to clean on the way out.

    You can't avoid all instances of nulls but having sensible defaults for missing values is useful.

    Just today I've run into a case where having NULL values as allowed an application to misrepresent stats because null values screwed up the calculations. We now have to face the Spanish Inquisition as to why this happened and also we now have a data trust issue we didn't have before.

  • David's points definitely make sense, but as he himself mentioned earlier, there may be instances where NULLs are required to represent <unknown>. If you don't need them, scrubbing them in the DAL on the way in is probably the best way to go.

    But, if you have to use them, and you're using .NET to present, you'll want to trap for DBNull-related errors. Otherwise your app will be throwing these quite a bit.

    Personally, I like Gus' argument for separating <unknown> and <don't care> values.

    Full disclosure: my company allows NULLs (probably more than we need to); both the .NET and SQL developers have been trained in handling them so we get by okay.

    Still they are like handguns: without proper awareness and training the results can be tragic.

  • David.Poole (11/12/2010)


    If you have to build application code to scrub nulls then surely it would have been better not to have them in the first place. Surely allowing nulls just adds one more thing to have to build tests for?

    If you have a DAL layer then clean on the way in to stop having to clean on the way out.

    You can't avoid all instances of nulls but having sensible defaults for missing values is useful.

    Just today I've run into a case where having NULL values as allowed an application to misrepresent stats because null values screwed up the calculations. We now have to face the Spanish Inquisition as to why this happened and also we now have a data trust issue we didn't have before.

    I would argue that you've had a data trust issue all along, but it's only blown up recently. Before, you were trusting data that hadn't been validated as useful, now you don't trust data because it's been shown to have problems. Either way, there's a problem.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • CELKO (11/9/2010)


    Do not use the old dialect != from C and Sybase.

    I've been trying to get people out of the habit of using != but they're just not buying my "<> is more readable". Do you have another reason to avoid it?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/12/2010)


    CELKO (11/9/2010)


    Do not use the old dialect != from C and Sybase.

    I've been trying to get people out of the habit of using != but they're just not buying my "<> is more readable". Do you have another reason to avoid it?

    Heh... one of the few times I actually agree with the guy and I get no response. 😉 Moving on... :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 23 total)

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