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

Question

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

edwardwill

SSCertifiable

Points: 5789
More actions
June 4, 2020 at 9:18 am

#3760340
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?

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

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

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 1

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: 5789 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

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.

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

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 4

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: 24670 More actions · Answer 5

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

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.

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

edwardwill SSCertifiable Points: 5789 More actions · Answer 7

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

schleep SSChampion Points: 12582 More actions · Answer 8

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: 9294 More actions · Answer 9

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.