January 13, 2009 at 8:06 pm
J (1/13/2009)
As far as a server option to make a WHERE clause required, I can see some benefit, but it really is to protect idiots from themselves.
If you type:
[font="Courier New"]DELETE MyTable
WHERE RecNum = 1[/font]
and then you are dumb enough to accidentally highlight just the first row and press F5 ...
Of course, if all of that happened you shouldn't have a tool that allows you to run an update or delete against a production database and taking the tools away from these people is probably a more effective solution.
You are being harsh today...
I agree that a low-caffeine-level developer MIGHT ** BY ACCIDENT ** fail to highlight the entire command. This is more of an OOPS! type of thing than idiocy or stupidity. Now, of course, the half-awake perpetrator did ask for it - and the consequences.
Seriously, anyone using Query Analyzer and invoking the DELETE command without EVER making this mistake, fell free to cast the first stone.
... (arrrgh ! that hurt!). What's next ? FROZEN flying pork chops ?
Maybe it would be safer to write
[font="Courier New"]DELETE FROM myTable WHERE ... ON THE SAME LINE.[/font]
Heh... right there with you, J. Here's my set of high impact pork chops...
If you (not you J, anyone!) EVER do a manual delete and you haven't started the code with a BEGIN TRANSACTION, then you're well deserving of anything that happens to you, your data, or the bloomin' brick your boss should hit you between the running lights with.
All this talk about a mandatory WHERE clause is just cannon fodder when it comes to truly protecting both the stupid and the folks that just haven't had enough coffee for the day (heh, like me, your choice!). 😀 If you really want to do it right, make it mandatory to have a transaction and make it so when you try to do a COMMIT, it reminds you of the record count, the server, and the database that you're trying to blow out of the water by mistake and then asks you the infamous and rather final ARE YOU SURE???? 😛
Heh... and to all you people who will complain about forgetting to do a COMMIT, you wouldn't if you actually typed BEGIN TRANSACTION... :hehe:
--Jeff Moden
January 14, 2009 at 6:48 am
Jeff Moden (1/13/2009)
GSquared (1/13/2009)
So, two mistakes, neither of which would have mattered all that much by itself, but together made a problem that mattered, which could have been prevented with a mandatory Where clause.BWAA-HAA!!! You forgot the 3rd mistake... you let stupid people near your database. 😛
Nah. As mentioned, this guy was usually just fine. In the year and a half I was there, this was his only error. It just happened to be a doozy!
On the other hand, I'm pretty much an adherent to the "let people make their own mistakes on their own time" school of thought. I don't care if someone doesn't wear a seatbelt, so long as I don't have to pay to clean them up off the sidewalk. I will, however, point out the advantages of seatbelts to them, just don't think it should be mandatory.
- 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
January 14, 2009 at 7:18 am
GSquared (1/13/2009)
Forgetting to highlight the Where clause is just one example.As a note, he had the production server and the dev server open in Management Studio at the same time, and thought he was testing in dev, but was actually testing in production. Again, there's a flaw in the presentation in Management Studio's default layout that makes that easy to do. So, two mistakes, neither of which would have mattered all that much by itself, but together made a problem that mattered, which could have been prevented with a mandatory Where clause.
This is why we do not allow access to development and production environments with the same ID. In general, we don't allow change access to a production environment to more than a few DBA's, whose job is to execute an approved script or scripts without making any changes to them. The DBA's use special ID's to access production data, and those ID's do not have access to any test or development servers. The ID's are used in Citrix or remote desktop connections that have a different appearance from normal desktops.
Ross
January 14, 2009 at 9:03 am
I have seen this technique used quite often when building querries on the fly. For example you may have a webpage where the user can select multiple criteria. If you are building the where clause on the fly you need to determine if this is the first item to add to the query in which case you add a WHERE plus the criteria. Subsequently you add a AND plus the criteria. This requires an if statement for every potential item you wish to add to see if it is the first. If you start out with a WHERE 1=1 you simply add an AND plus the appropriate condition for each search criteria that you wish to add.
January 14, 2009 at 11:39 am
Ross McMicken (1/14/2009)
GSquared (1/13/2009)
Forgetting to highlight the Where clause is just one example.As a note, he had the production server and the dev server open in Management Studio at the same time, and thought he was testing in dev, but was actually testing in production. Again, there's a flaw in the presentation in Management Studio's default layout that makes that easy to do. So, two mistakes, neither of which would have mattered all that much by itself, but together made a problem that mattered, which could have been prevented with a mandatory Where clause.
This is why we do not allow access to development and production environments with the same ID. In general, we don't allow change access to a production environment to more than a few DBA's, whose job is to execute an approved script or scripts without making any changes to them. The DBA's use special ID's to access production data, and those ID's do not have access to any test or development servers. The ID's are used in Citrix or remote desktop connections that have a different appearance from normal desktops.
Ross
Yep. That's how it should be. Wasn't at that shop, but should have been. That's a whole different situation though. And one of the reasons I say a simple solution like manadatory Where clauses won't solve much, if anything.
- 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
January 14, 2009 at 4:04 pm
If you (not you J, anyone!) EVER do a manual delete and you haven't started the code with a BEGIN TRANSACTION, then you're well deserving of anything that happens to you, your data, or the bloomin' brick your boss should hit you between the running lights with.
All this talk about a mandatory WHERE clause is just cannon fodder when it comes to truly protecting both the stupid and the folks that just haven't had enough coffee for the day (heh, like me, your choice!). 😀 If you really want to do it right, make it mandatory to have a transaction and make it so when you try to do a COMMIT, it reminds you of the record count, the server, and the database that you're trying to blow out of the water by mistake and then asks you the infamous and rather final ARE YOU SURE???? 😛
Heh... and to all you people who will complain about forgetting to do a COMMIT, you wouldn't if you actually typed BEGIN TRANSACTION... :hehe:
I'm with you Jeff. I always use BEGIN TRANSACTION when performing a manual delete. I also use it when I am doing an update also, of course that can depend on the amount of rows in a table. These words have saved my butt quite a few times.
I also have used the WHERE 1=1 when debugging, but I would agree with most that it should be removed before being pushed to production.
January 14, 2009 at 7:58 pm
John Dempsey (1/14/2009)
I'm with you Jeff. I always use BEGIN TRANSACTION when performing a manual delete. I also use it when I am doing an update also, of course that can depend on the amount of rows in a table. These words have saved my butt quite a few times.
I agree... any data modification gets a BEGIN TRAN when I do it. I also go one step further, though... I do an equivalent SELECT and eyeball the data it returns, first. That does two things... allows me to see what will actually change and, if that's ok, gives me the row count I need to match when I actually fire off the modification.
--Jeff Moden
January 15, 2009 at 6:46 am
Jeff Moden (1/14/2009)
John Dempsey (1/14/2009)
I'm with you Jeff. I always use BEGIN TRANSACTION when performing a manual delete. I also use it when I am doing an update also, of course that can depend on the amount of rows in a table. These words have saved my butt quite a few times.I agree... any data modification gets a BEGIN TRAN when I do it. I also go one step further, though... I do an equivalent SELECT and eyeball the data it returns, first. That does two things... allows me to see what will actually change and, if that's ok, gives me the row count I need to match when I actually fire off the modification.
I do pretty much the same thing. First time I test the delete/update, I have a begin transaction, and a rollback. Started doing that after one time when I got distracted between when I told it to execute and when it finished, and left a transaction open on a production server for about five minutes too long. All kinds of stupid deadlocks because of that. So now, instead of just having a begin, and running a commit at the end, I use a rollback, check the number of rows and make sure it looks right, then change the rollback to commit and run again.
- 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
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply