July 13, 2012 at 1:42 am
david.wright-948385 (7/12/2012)
djackson 22568 (7/12/2012)
....A paranoid dba is a good dba 🙂
...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...
(Oh - please leave me alone prefixing a fieldname - the boolean-values are the only one I do it w/ - as a marker and reminder what this field is about...)
and
Frank W Fulton Jr (7/12/2012)
I was once told "if you are not making mistakes, chances are you aren't doing anything".Every one makes mistakes, the important thing is to learn from them
Well - I can agree w/ the first sentence - but mistakes are there for us to recognize when we make them again... 😉
...like the guy w/ the faulty if-statements.
July 13, 2012 at 11:23 am
Many times in life, I have found, it's not really about being right or wrong...it's about being at a point in life of having the guts (or balls) to get off the dime and just make a decision. Many times indecision, and second-guessing yourself, can be downright paralyzing, and sometimes ultimately, can have tragic results. Sometimes, you just gotta get off the pot and go "all in" and go with what you believe, right or wrong. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
July 13, 2012 at 3:00 pm
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.
July 13, 2012 at 6:56 pm
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2012 at 10:22 am
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?
July 14, 2012 at 2:37 pm
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?
We're sliding off topic here, but anyways...
I personally would never use NULL as an indicator - actually I prefer not to have NULL anywhere in my tables.
Yeah - it's good to now when it was deleted but for that purposes I use a custom log.
Ok, considered, and dismissed. Hehe... :smooooth:
August 6, 2012 at 10:32 am
For instance, I recently said at my workplace that a database backup created on one version of SQL Server could not be restored onto a different version of SQL Server.
Not to get too far afield of your main point, but could you clarify this? As far as I know you can't restore to a prior version of sql server, but can to a subsequent version. If you can restore to a prior version, I'd love to hear that I was wrong (and how to do so).
August 6, 2012 at 11:16 am
john.moreno (8/6/2012)
For instance, I recently said at my workplace that a database backup created on one version of SQL Server could not be restored onto a different version of SQL Server.
Not to get too far afield of your main point, but could you clarify this? As far as I know you can't restore to a prior version of sql server, but can to a subsequent version. If you can restore to a prior version, I'd love to hear that I was wrong (and how to do so).
You can restore to a later version, within limits, but not to an earlier version. That's correct.
General rule of thumb is 2 versions earlier is okay.
Oh, and a very common mistake on this is to set a database from a later version, into a prior compatibility mode, then back it up and try to restore it on a prior version of SQL Server. Doesn't work. Can't set a database to Compat 80, from SQL 2008, and restore it on SQL 2000. Doesn't work. For relatively obvious reasons, lots of people think it will, and can't figure out why it doesn't work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 11:38 am
GSquared (8/6/2012)Oh, and a very common mistake on this is to set a database from a later version, into a prior compatibility mode, then back it up and try to restore it on a prior version of SQL Server. Doesn't work. Can't set a database to Compat 80, from SQL 2008, and restore it on SQL 2000. Doesn't work. For relatively obvious reasons, lots of people think it will, and can't figure out why it doesn't work.
I fell into this trap too...
Hakim Ali
www.sqlzen.com
August 6, 2012 at 11:39 am
john.moreno (8/6/2012)
For instance, I recently said at my workplace that a database backup created on one version of SQL Server could not be restored onto a different version of SQL Server.
Not to get too far afield of your main point, but could you clarify this? As far as I know you can't restore to a prior version of sql server, but can to a subsequent version. If you can restore to a prior version, I'd love to hear that I was wrong (and how to do so).
Yes, you are right. I had said that a backup of 2005 could not be restored to 2008. I was wrong, of course.
Hakim Ali
www.sqlzen.com
August 6, 2012 at 12:20 pm
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?
While I personally find "virtual deletion" to be a horrible, horrible solution in the vast majority of cases, having an arbitrarily late Deleted value does make sense compared to NULL, when it comes to query optimization.
It allows you to avoid OR statements in your Where clauses, which makes for better chances for an index seek instead of a scan.
SELECT *
FROM dbo.MyTable
WHERE Deleted > @MyInputParam;
will likely seek better than
SELECT *
FROM dbo.MyTable
WHERE
Deleted > @MyInputParam
OR
Deleted IS NULL;
Slightly easier to write (one less line of code, really), but can make a difference in execution plans that may actually matter.
Of course, it'll mess with the selectivity/cardinality, if a huge percentage of your rows are "undeleted", which may just trash every query in your database all by itself, in terms of performance.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 12:27 pm
On the date-math item, it's not so applicable to a delete date, but it is to others.
Since you may have to declare a non-null "end date" that has to be greater-than any possible "DateColumn" value, in order to take into account the ones that are "at the end of time", and you can't assign a value of 1 Jan 10,000 (it only goes up to 31 Dec 9999) without getting an error, you need to leave one for padding.
Say you have a table with "ValidFrom" and "ValidTo" columns, for example. If you don't want an end-date on a row, you put in 31 Dec 9999, because that's the max allowed value for DateTime datatype.
Then you want to query everything that's valid, so you query "Where ValidFrom > @InputParam1 and ValidTo < @InputParam2", well, you can't do that now, since you'd have to assign 1 Jan 10,000 to @InputParam2. So put the "end of time" value as 30 Dec, and assign the parameter 31 Dec, both in 9999, and now your query will work without an error message.
Make sense?
(Complexities like these are one of the myriad of reasons I consider such systems horribly flawed in the vast majority of cases. They make documentation more difficult. They increase the odds of coding failure. They increase training time for new developers. The list goes on, and on, and on.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 12:58 am
GSquared (8/6/2012)
Complexities like these are one of the myriad of reasons I consider such systems horribly flawed in the vast majority of cases. They make documentation more difficult. They increase the odds of coding failure. They increase training time for new developers. The list goes on, and on, and on.
Amen to that! I thought it was odd that nobody else expressed negative opinions about the design pattern, so glad to see I have company; I agree with all your specific reasons.
Not that there isn't room in certain designs for the pattern, but I have suffered through an environment where columns like EffectiveFrom, EffectiveTo and DateArchived were mandated by coding standards, even though there was no accompanying agreed standard of how one was supposed to use and interpret these, and in many cases they weren't semantically meaningful at all. People regularly screw up when querying simple systems - why would they possibly fare better if you force an unnecessarily complicated design upon them?
(ETA: By the way, I am referring to a transactional system here, and not a data warehouse. I have no quarrels with SCDs)
August 7, 2012 at 6:06 am
Ewald Cress (8/7/2012)
GSquared (8/6/2012)
Complexities like these are one of the myriad of reasons I consider such systems horribly flawed in the vast majority of cases. They make documentation more difficult. They increase the odds of coding failure. They increase training time for new developers. The list goes on, and on, and on.Amen to that! I thought it was odd that nobody else expressed negative opinions about the design pattern, so glad to see I have company; I agree with all your specific reasons.
Not that there isn't room in certain designs for the pattern, but I have suffered through an environment where columns like EffectiveFrom, EffectiveTo and DateArchived were mandated by coding standards, even though there was no accompanying agreed standard of how one was supposed to use and interpret these, and in many cases they weren't semantically meaningful at all. People regularly screw up when querying simple systems - why would they possibly fare better if you force an unnecessarily complicated design upon them?
(ETA: By the way, I am referring to a transactional system here, and not a data warehouse. I have no quarrels with SCDs)
Yeah, SCDs in warehouses are a different thing. If that's what this is about, then nix my negative on it. I've just been burned too often by poorly designed and implemented virtual-delete/update OLTP databases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2012 at 2:50 am
GSquared (8/6/2012)
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.
...
While I personally find "virtual deletion" to be a horrible, horrible solution in the vast majority of cases, having an arbitrarily late Deleted value does make sense compared to NULL, when it comes to query optimization.
...
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... :-))
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply