Is it acceptable to use the presence of a value in a NULLable column as a flag?

  • edwardwill

    SSCertifiable

    Points: 5315

    I have a stored procedure that processes rows in a table. The stored procedure is called by an external process (Jitterbit, though I don't believe that is relevant). Occasionally the stored procedure throws an exception which is caught in a TRY CATCH block. My client wants this to work slightly differently; they want the error to be written back to the table (e.g. create a new column for the error information) which can then be periodically aggregated into a notification email. There will be another bit column (DEFAULT 0) that will be set to 1 when an error in that row is included in a notification email (so that the email is sent only once).

    My question is this. If I make the column for the error message NULLable, and therefore do something like:

    SELECT * FROM MyTable WHERE ErrorMessage IS NOT NULL AND EmailSent = 0;

    is this an acceptable approach - to use the presence (or absence) of data in a column as a flag? My inner pedant thinks that another BIT column ought to be used - for example:

    SELECT * FROM MyTable WHERE ErrorCreated = 1 AND EmailSent = 0;

    Am I being pedantic?

  • jonathan.crawford

    SSCertifiable

    Points: 6582

    If you have ErrorCreated, then you have to store what the message is separately. If you use the presence or absence of a message in ErrorMessage, then you know what the error is already. I think either are acceptable, I would go with the first just to avoid the extra column myself.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • edwardwill

    SSCertifiable

    Points: 5315

    Thanks Jonathan.  That is my inclination, but I would always prefer to follow accepted guidelines, if there are any ...

  • Jeff Moden

    SSC Guru

    Points: 997112

    Generally, I try to avoid all "IsPresent", "IsCurrent", "IsActive", and, for sure "IsInactive" and "IsNotCurrent" columns, etc, etc.  The data tells it all.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • jonathan.crawford

    SSCertifiable

    Points: 6582

    You know what, is there a timestamp on when the process ran on that table? createdate or something? you could just set up the notification email to run on a regular basis, for a date range, including everything created within that daterange. Then you could just ignore the flag entirely. Next email won't pick up the older stuff, so no worries about including it multiple times

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Mr. Brian Gale

    SSC-Insane

    Points: 23165

    I think either are acceptable, but I think one will have better performance.  I am pretty sure "NOT NULL" is not sargable so you may have a performance hit by using it.  Plus I am fairly certain that comparing int values is faster than comparing string values.

    That being said, the emailsent=0 I expect will reduce the search scope a lot so the number of rows returned would be small and the comparison for NOT NULL won't touch that many columns.

    And, depending on  what is stored in MyTable, and the number of possible values for ErrorMessage, it may make sense to normalize that data.  If for example, if each error message is approximately 1 KB in size, you could save roughly 1 KB (as an int is only 4 bytes) per row with a message by having a lookup table with an int ID column and the message text.  If you use this approach as well, you could save the IS NOT NULL too by having 0 be success and greater than 0 failure.  So ErrorMessage becomes ErrorMessageID which is an int, and your check is where ErrorMessageID > 0.

    May be overkill for your situation, but may be helpful as the table grows in size.

  • Jeff Moden

    SSC Guru

    Points: 997112

    Mr. Brian Gale wrote:

    I think either are acceptable, but I think one will have better performance.  I am pretty sure "NOT NULL" is not sargable so you may have a performance hit by using it.  Plus I am fairly certain that comparing int values is faster than comparing string values.

    I also avoid NULL values for that very reason and the reason that, on variable width columns, ANY change from NULL to even an empty string constitutes an "ExpAnsive" update that will cause page splits and the ensuing fragmentation.  I find also find that most people end up using BIT values for flags, which are not only a pain but flags also need to be maintained.  Worse yet, I find that people end up not trusting their own flags and end up using criteria that includes both the base column and the flag column. Additionally, it's better to have a CHAR(1) column to hold a status to replace what a lot of people end up having separate bit columns for that end up being mutually exclusive anyway.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • edwardwill

    SSCertifiable

    Points: 5315

    Thanks everyone.  I've gone with the presence or absence of data in the NULLable error message column.

  • schleep

    SSChampion

    Points: 12551

    You say "occasional" errors. For me, this would go into a separate table with a FK to the main table, the error, and the emailed status (and date?)

  • jcelko212 32090

    SSCrazy Eights

    Points: 9028

    I do not use assembly language style bit flags at all. See; https://www.red-gate.com/simple-talk/sql/t-sql-programming/bit-of-a-problem/

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 10 posts - 1 through 10 (of 10 total)

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