Why It's Good To Be Wrong

Question

Why It's Good To Be Wrong

Viewing 2 posts - 46 through 46 (of 46 total)

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 1

Ola L Martins-329921 (8/14/2012)
I'd like to differ on the "virtual deletion" - in my opinion - or better put - in my mind - the deleted-column (in any form) does not reflect the state of the row itself, but a status of the contained data (order, product, whatever). (Splitting ends, I know... :-))

Heh... "Splitting ends" is an understatement here. 🙂

--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 SSC Guru Points: 1003869 More actions · Answer 2

Mike Dougherty-384281 (7/14/2012)
Jeff Moden (7/13/2012)
Mike Dougherty-384281 (7/13/2012)
Ola L Martins-329921 (7/13/2012)
...hence I never delete a-n-y-t-h-i-n-g from the tables. I use a switch "bit_deleted" and a view to get the not-deleted records...
consider a deleted [small]datetime: null indicates not deleted else it contains WHEN it was deleted.
Agreed. The only thing different that I do is that I use 9999-12-30 for a "non-deleted" date so that I can make the column NOT NULL and a bit easier to index. The reason I don't use 9999-12-31 is so I can support certain types of date math to do lookups on other columns (such as EndDate) and I just carry that little standard over into DateDeleted columns for consistency's sake.
I first read this as extreme sarcasm: using two arbitrary date values to "avoid null" and turn the datetime field back into the bit column Ola was using. Then I realized you would have followed-up sarcasm with a helpful suggestion for improvement.
Then I considered seeing 9999-12-30 for 99% of the rows of my table and thought that would be weird and wrong. My bias for the NULL value to indicate not-deleted comes from habit - and using null for a sentinel value is not really different than 9999-12-30 to mean not-deleted. Fortunately its far enough in the future that we're not likely to accidentally cross that time (caveat: that value won't fit in a smalldatetime; smalldatetime's max value will occur during our professional careers)
How does the nullability of this deletedate field affect an index? I admit having to write where clauses that test for "field is null OR field {op} value" can be tedious and redundant.
Also, could you give some examples of the types of date math you do that makes 9999-12-30 a better choice for not-deleted than 9999-12-13?

Hi Mike,

Sorry for the late reply. You're correct. There was no sarcasm nor irony in what I wrote. I use 9999-12-30 to indicate what you said you would normally use a NULL EndDate to indicate. Gus did a really good job of explaining the date math behind it (instead of using 9999-12-31) all to avoid the use of OR.

As for SmallDateTime... I don't ever use it for a couple of reasons but the most significant of them is rounding. If you happen to send it something like...

SELECT CAST('2000-01-01 23:59:30' AS SMALLDATETIME)

... the bloody thing rounds up to the next day. While I expect that and know that, I can count on one hand the number of developers I've ever met that actually know that. To me, it's simply worth the extra 4 bytes of width per column to implicitly avoid such problems and to have to have a double standard for an "end-of-time" date.

I suppose that if I were relegated to working with SQL Express, then I might be a wee bit more conservative with how wide my date columns actually are.

As a (possibly interesting) side bar, I always write my date criteria as CLOSED/OPEN (>= and <) even when writing against a DATE datatype for two reasons... I only have to be well practiced at 1 standard and it makes the code bullet proof for all of the current date/time datatypes. I have had it where someone decided they made a mistake and converted the data type of a DATE column to DATETIME because they suddenly found out time was important to them. When that happened, I didn't have to change a thing in the code that I had written.

{EDIT/PS}

Fortunately its far enough in the future that we're not likely to accidentally cross that time (caveat: that value won't fit in a smalldatetime; smalldatetime's max value will occur during our professional careers)

I agree... I'll only be 127 when 2079-05-28 occurs. 😛

--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)