• (cs) in reply to SurturZ
    SurturZ:
    dew|frost:
    JV:
    I agree with this. But being somewhat of a newbie to SQL, I've always declared my columns with a NOT NULL constraint to avoid the whole null issue. Are there downside to doing that?

    The downside / specific reason is, what do you do when somebody has no, say, fax number?

    Store it as an empty string!

    Are you REALLY trying to distinguish between when a user never enters a fax number, and when they type in a fax number and then delete it? Users cannot enter NULLs into the user interface, so to me, user-entered fields should never support NULL.

    What is a user interface? What is a "user"? An actual user? An automated job? The fact that users cannot (or should not be able to) insert NULL values is what make SQL NULL so valuable. They define an initial state when it comes to fields, and they can be indicators of incomplete initialization (as opposed to wrong initialization due to user/programming errors.)

    In general, entering a blank string instead of a SQL NULL is a bad idea; a very sophomorish design decision no matter how you cut it.

    Notice that I said in general. If your database is accessed in complete isolation, and your app is the only one permitted to do inserts or updates, then, by all means. You have the freedom to develop your application logic with the capacity to treat blank strings and SQL NULL values as equivalent.

    BUT, most database systems of transactional relevance are not like that. Multiple database user accounts (representing applications, database/cron jobs or async event listeners) might insert, update or delete data, data accumulated for years, if not decades (though that's an extreme case). SQL GETS via store procedures might execute auditing triggers; many distinct writers, few distinct consumers; many distinct consumers; few distinct writers; or worse; crap like that which might me maintained in different code bases by different teams/departments... if you are lucky.

    At that point then, you are wise to allow your tables to include SQL NULLS.

    No fax number? Fine, leave the field as NULL.

    Find a empty string in place? Then you know that there is a bug.

    Perhaps your app is allowing (by mistake) to insert empty strings.

    Perhaps a user enters a valid fax number, but somewhere in transit, there is a bug that causes that information to get lost, replaced by an empty string.

    Remember than in Java, C# and the like it is good practice to let unitialized String variables (or any type for that matter) set to null as opposed to ""?

    Same principle with SQL NULL. Using empty strings (or any form of magic value/number) to denote lack of initialization has been one of the worst and most common coding WTFs I've had the pain to suffer (and debug) from.

    In fact, the existence of SQL NULL is intended as a way to implement tertiary logic; true,false,unknown (or more to the point, correct-value, with-incorrect-value, not-initialized/not-initialized-yet).

    Obviously this is rather esoteric and we as developers do not think like that every day when we develop and maintain RDBMS-accessing code. But it does provide the ground for differentiating the case of invalid values (which can be traced to a root bug) vs lack of initialization (which is almost always the initial state of things.)

    SurturZ:
    Date fields might be an exception, but I personally find it more practical to have a magic date that means "no date". At least then you'll only get a wtf date on your printout if you make a coding error, rather than crashing the app.

    Oh, magic values. Yummy. Invalid input should never crash your app... if you know how to code, that is.

    Bad input should provide an error to the user. If it passes through (after all, one can always have a bug, it's inevitable), the back end should be codified with validators that can detect invalid input, printing to the logs to that effect and notify DBA's or IT support...

    ... if you know how to code, if you are sufficiently decent soft. architecture skills, and if you work in a competent environment.

    My first job in 94 involved working with this WTFer, programming reports in PICK systems (using PICK Basic). In that flavor of BASIC, the interpreter would crap out and crash whenever it encountered an unitialized value. It would tell you where the error was, but it would crap out and die.

    So the WTFer had the habit of initializing all his vars to empty strings "just so that the reports wouldn't crash in front of his users". They might get some weird report with meaningless, useless data, but they would not crash.

    How brilliant.

    [image]

    Since he initialized all his vars to "", the BASIC interpreter would not crash telling you what var or line number caused the crash. It made it f* impossible to trace the root cause. It was a time where we literally had to put grass skirt grasses on, throw some chicken bones at the console and pray to the Sky Lords for a miracle, a beam of light that would point out where the error originated from.

    The greatest lesson I learned from that first job is that there are people out there, making a living out of software development... and that they shouldn't.

  • another idiot (unregistered) in reply to Vilx-

    There is one caveat to using varchar(max) ... you can't index it.

  • another idiot (unregistered) in reply to another idiot
    another idiot:
    There is one caveat to using varchar(max) ... you can't index it.

    oops fulltext is ok, use as a key index isn't ...

  • Captain Teeb (unregistered)

    Hahahaha, welcome to developer-land!

    I once had one like this, only it was inside a loop. It needed 8 million soft parses (Oracle DB) to populate a 25,000-row table.

  • Nuero (unregistered) in reply to tin

    Tecnicaly the max length for a phone number is 14 or at least in ISO standards

  • AdT (unregistered) in reply to SurturZ
    SurturZ:
    Users cannot enter NULLs into the user interface, so to me, user-entered fields should never support NULL.

    LOL, this reminds me of the time I had to convince a non-geek that the data he entered into the computer was not stored "inside the window" and that therefore it was perfectly possible for two or more windows to display views of the same data. Seriously, if you as an application programmer are unwilling to use beneficial abstractions simply because they are not obvious to the application user, I can't help but shudder at the thought of the resulting code.

    (And thanks to luis.espinal)

  • falco (unregistered)

    It seems to me that most of you is using the word "SQL" instead of "M$ SQL Dialect" :-)

Leave a comment on “A Confusing SELECTion”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article