Dangers of update statement

I'm always a bit concerned when doing an update statement in case there is a
bug that causes it to update an entire table or too many records. Out of all
the programming techniques the update statement would have to have close to
the worst concequences for a bug, imo. I'm curious what techniques people
use to avoid this problem (besides the obvious such as testing).
The reason I'm asking is I found an update statement which should have had a
"where ID = @.ID" but I just plain forgot the where clause. This went out to
customers but through some miracle never got called. It was within a couple
of If statement and was only called in unusual circumstance which luckily
never happened.
Cheers,
MichaelMichael C wrote:
> I'm always a bit concerned when doing an update statement in case
> there is a bug that causes it to update an entire table or too many
> records. Out of all the programming techniques the update statement
> would have to have close to the worst concequences for a bug, imo.
> I'm curious what techniques people use to avoid this problem (besides
> the obvious such as testing).
> The reason I'm asking is I found an update statement which should
> have had a "where ID = @.ID" but I just plain forgot the where clause.
> This went out to customers but through some miracle never got called.
> It was within a couple of If statement and was only called in unusual
> circumstance which luckily never happened.
> Cheers,
> Michael
I'd say there was a serious gap in testing that particular procedure :-)
Every procedure should be tested with all possible inputs and the
outputs clearly examined and documented. As a part of the stored
procedure development process, the developer should clearly examine the
procedure code and design a set of test calls that will attack all of
the source. Also make sure that NULL parameter values are tested as well
where allowed. This could result in a lot of test calls, but it's much
easier to set this up during development than it is to design during an
application test. As you saw, it possible an application will never call
a stored procedure with the parameters that trigger a major problem. In
that case, the development side is the only way to catch these problems.
This could be an internal process where developers clearly document the
test cases (call text and expected outpu). You could document these
items along with the stored procedure text right in your version control
system. Another option is to use a framework for testing that can help
you automate unit testing. An open-source project call TSQL Unit is such
an option (although it hasn't been updated in a while):
http://msdn.microsoft.com/library/d...r />
p04i1.asp
http://sourceforge.net/projects/tsqlunit
That's not to say an error can't creep in occasionally, but a documented
development and testing process will go a long way in eliminating these
types of errors.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||My post errored out so I am not sure if my earlies response got posted.
You could put begin tran before you update and commit or rollback tran after
checking your results.
Check out the following example:
set nocount on
go
create table test(
c1 int not null,
c2 int not null)
go
insert test values(1,1)
insert test values(2,1)
insert test values(3,1)
go
select * from test
go
begin tran
update test set c2= 333 where c1=2
select * from test
/*
c1 c2
-- --
1 1
2 333
3 1
*/
rollback tran
select * from test
/*
c1 c2
-- --
1 1
2 1
3 1
*/
go
drop table test
go
HTH...
http://zulfiqar.typepad.com
BSEE, MCP
"Michael C" wrote:

> I'm always a bit concerned when doing an update statement in case there is
a
> bug that causes it to update an entire table or too many records. Out of a
ll
> the programming techniques the update statement would have to have close t
o
> the worst concequences for a bug, imo. I'm curious what techniques people
> use to avoid this problem (besides the obvious such as testing).
> The reason I'm asking is I found an update statement which should have had
a
> "where ID = @.ID" but I just plain forgot the where clause. This went out t
o
> customers but through some miracle never got called. It was within a coupl
e
> of If statement and was only called in unusual circumstance which luckily
> never happened.
> Cheers,
> Michael
>
>|||On Fri, 19 Aug 2005 11:52:02 +1000, Michael C wrote:

>I'm always a bit concerned when doing an update statement in case there is
a
>bug that causes it to update an entire table or too many records. Out of al
l
>the programming techniques the update statement would have to have close to
>the worst concequences for a bug, imo.
Hi Michael,
I'd say that the DELETE is possibly even more dangerous.
In one of my first jobs in the SQL Server world, my boss was writing and
testing a query to remove erroneous rows from the production database
that were introduced by a bug. Here's how he tested his delete statement
to check that he got the where clause just right:
-- DELETE FROM TheTable
select * from TheTable
WHERE ....
AND ....
go
Once he had the wherer clause tweaked to return just the rows that had
to be deleted, he removed the comment in front of the first line and
clicked the execute button...
After a few minutes, he asked my more experienced coworker if he
understood why the query was taking so long. The coworker then
immediately rushed to his own PC and issued a kill command.
I think that this was the only time that we were actually glad that this
table was loaded with trigger code that was slower than a turtle with
two crippled legs.

> I'm curious what techniques people
>use to avoid this problem (besides the obvious such as testing).
For ad-hoc queries that I want to test first, I always START to type
this:
BEGIN TRAN
go
go
ROLLBACK TRAN
go
After that, I position the cursor between the two go's and start typing
the selects that show the result of my statement, and then the update,
insert, or delete statement itself.
(The extra go before the ROLLBACK ensures it gets executed even if the
query has an error that aborts the batch)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:beecg1hp4o6lk251uv01l62c07trnm4rnb@.
4ax.com...
> I'd say that the DELETE is possibly even more dangerous.
> In one of my first jobs in the SQL Server world, my boss was writing and
> testing a query to remove erroneous rows from the production database
> that were introduced by a bug. Here's how he tested his delete statement
> to check that he got the where clause just right:
> -- DELETE FROM TheTable
> select * from TheTable
> WHERE ....
> AND ....
> go
We had a similar problem except much worse. One particular database had no
referential integrity and someone thought they'd issue a delete statement to
delete orphan records. Problem was they forgot the where clause altogether
and deleted all the records in the table. This went out to 300 customers but
I think the problem was found and fixed fairly quickly so not too many
people encountered the problem. I pointed out that the delete would have
failed if we had integrity but it seemed to fall on deaf ears.
Michael|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:erhULaHpFHA.3376@.TK2MSFTNGP10.phx.gbl...
> I'd say there was a serious gap in testing that particular procedure :-)
> Every procedure should be tested with all possible inputs and the outputs
> clearly examined and documented. As a part of the stored procedure
> development process, the developer should clearly examine the procedure
> code and design a set of test calls that will attack all of the source.
> Also make sure that NULL parameter values are tested as well where
> allowed. This could result in a lot of test calls, but it's much easier to
> set this up during development than it is to design during an application
> test. As you saw, it possible an application will never call a stored
> procedure with the parameters that trigger a major problem. In that case,
> the development side is the only way to catch these problems.
You live in a very different world to me :-) Our projects are fairly rushed
and disorganised and testing is fairly poor. I'd love it to be different but
the only way to do that is change jobs.
Michael|||On Mon, 22 Aug 2005 13:57:38 +1000, Michael C wrote:
(snip)
>You live in a very different world to me :-) Our projects are fairly rushed
>and disorganised and testing is fairly poor. I'd love it to be different bu
t
>the only way to do that is change jobs.
Hi Michael,
But isn't the amount of time you have to spend repairing things even
more than the time you would have spent testing?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:nulkg11ab4m7cr5dqiot9re6atbadmnkbo@.
4ax.com...
> But isn't the amount of time you have to spend repairing things even
> more than the time you would have spent testing?
That's a good question and I'd probably say yes but it's just the way it is
here. There's never any time to do it properly but there's always plenty of
time to fix it. There's always someone who needs something urgently and
always a reason to rush it. I keep explaining that if we'd been doing this
three years ago there'd be someone who needed it urgently then but no one
seems to listen to that. Nothing here get approved if it's over 2 months, it
doesn't matter if it runs over time as long the initial estimate is for 2
months or less. I could go on for hours about this. :-)
Michael|||Michael C wrote:
> We had a similar problem except much worse. One particular database had no
> referential integrity and someone thought they'd issue a delete statement
to
> delete orphan records. Problem was they forgot the where clause altogether
> and deleted all the records in the table. This went out to 300 customers b
ut
> I think the problem was found and fixed fairly quickly so not too many
> people encountered the problem. I pointed out that the delete would have
> failed if we had integrity but it seemed to fall on deaf ears.
> Michael
Hows the job hunting going? Seriously - do your customers know your
name, does your name get associated with these problems? Sooner or
later (probably), your customers are going to move to a supplier who
does care to get these things right in the first place. I'd get out
before the going gets bad (but make sure to include a good lengthy
sermon in your notice)
Just my two-penneth.
Damien|||Add this to your approval form:
1. This job must be done quick...
2. This job must be well done...
3. This job must be done cheap...
PICK 2 OUT OF 3.
Too bad a lot of bosses choose 1 and 3.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:%23DhnabQqFHA.1556@.TK2MSFTNGP12.phx.gbl...
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:nulkg11ab4m7cr5dqiot9re6atbadmnkbo@.
4ax.com...
> That's a good question and I'd probably say yes but it's just the way it
> is here. There's never any time to do it properly but there's always
> plenty of time to fix it. There's always someone who needs something
> urgently and always a reason to rush it. I keep explaining that if we'd
> been doing this three years ago there'd be someone who needed it urgently
> then but no one seems to listen to that. Nothing here get approved if it's
> over 2 months, it doesn't matter if it runs over time as long the initial
> estimate is for 2 months or less. I could go on for hours about this. :-)
> Michael
>