I have a table with a column named DeleteCode which has the following values and their meanings:
NULL = Active record not yet processed
0 = Active record already processed
R = Deleted by Rescheduling
M = Deleted by Modification
C = Deleted by Cancellation
N = Deleted by No Show
So I have the following options whenever I want to query the active records in this table:
(1) SELECT * FROM MyTable WHERE IsNull(DeleteCode,'0') = '0'
(2) SELECT * FROM MyTable WHERE DeleteCode IS NULL OR DeleteCode = '0'
Which of the above would perform faster?
Also, would it be worthwhile to create an index on the DeleteCode column or is it not necessary since the possible values of DeleteCode are only very few?
Never use ISNULL in a WHERE or JOIN clause because it could hurt performance if an index is available on the column. But, you say, there is no index on that column so either is OK, right? NO. After all, an index could be created on that column tomorrow or next week.
So, would it be worthwhile to create an index on the DeleteCode column or is it not necessary since the possible values of DeleteCode are only very few?
Not likely needed for specifically a DeleteCode column, assuming that almost all rows are not deleted.
However, if almost no rows are deleted, and you have a process that looks at only deleted rows (DeleteCode = '1'), then it makes sense to create a filtered index on "WHERE DeleteCode = '1' ".
To get any more specific about indexes, you have to look at SQL stats regarding the current index(es) and missing indexes.
Scott, I don't currently have any process that looks only for deleted rows. All of my processes look only for active rows. So would it help a lot if I create the following index?
CREATE INDEX IX_DeleteCode_Filtered_IsActive
ON MyTable(DeleteCode)
WHERE DeleteCode IS NULL OR DeleteCode = '0'
That depends too. IF it's possible that at any point you don't know whether a row is deleted or not -- for example, new rows might already be expired -- then NULL, representing unknown, would make sense.
Totally agreed but such logic always requires additional code and frequently results in people writing code such as the first bit of code the OP wrote, which you correctly identified as something that should never be done.
I'll also suggest that if the status for a new row is truly unknown, then there's a bigger problem with both the requirements for the code and the code itself.
My suggestion is to never allow NULLs for a status column even if you have have to use a status of (for example) "0" to represent unknown. Yup... I agree that NULL means "Unknown" but it's a programming PITA that's just as bad as using NULL for EndDates.
I agree that a lot of people rail against defining a status of "Unknown" as anything other than a NULL status. To appease those folks (and using the previous example), make the column NOT NULL and use a default status of "0" for "NewRow" instead of "Unknown" because "NewRow" is the truest status in this case.
yeah I like my statuses to be explicitly set. it also helps for the next dev or dba that comes along to know exactly what it means and not something implicit.
If, say, 95+% of the rows are undeleted, how would this code make it faster than a standard index on DeleteCode? A filtered index on 95+% of rows just doesn't make a lot of sense to me.
