UPDATE on a Heap gone bad...

Question

UPDATE on a Heap gone bad...

Viewing 11 posts - 16 through 25 (of 25 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

I once worked at a place where a lack-of-WHERE-clause-highlighting issue produced the mandate that everyone should connect with IMPLICIT_TRANSACTIONS ON. It can be a bit of a PITA sometimes (especially with people not realizing that SELECT opens a transaction, or with people forgetting to COMMIT or ROLLBACK) but it did work quite well in the end. There you go, NZD0.02 added.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

btw, one way this can easily happen (thought of earlier, but didn't mention as it requires someone to have done silly things to the table)

CREATE TABLE ThisIsAHeap (

Column1 INT IDENTITY,

ColumnA CHAR(1),

ColumnB CHAR(1)

)

-- 505 rows

SET IDENTITY_INSERT ThisIsAHeap ON

INSERT INTO ThisIsAHeap (Column1, ColumnA, ColumnB)

SELECT 123, LEFT(NAME,1), RIGHT(NAME,1) FROM sys.columns AS c

SET IDENTITY_INSERT ThisIsAHeap OFF

UPDATE [dbo].ThisIsAHeap

SET ColumnA = 'X', ColumnB = 'Y'

WHERE Column1 = 123

(505 row(s) affected)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Michael Valentine Jones (8/26/2011)
This is a good illustration of why you should always run adhoc updates inside a transaction.
If you see the wrong number or rows updated, you can just roll it back. If everything looks OK, then commit it
-- Check expected number of rows to be updated
select count(*) from [dbo].
WHERE Column1 = 123
begin transaction
UPDATE [dbo].
SET ColumnA = X, ColumnB = Y
WHERE Column1 = 123
/*
rollback
commit
*/

THAT, good Sir, is the best recommendation, yet!

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

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 4

Gail - thank for having brought this up. The values in my SET command were treated as strings, but the WHERE value was an INT, and thus I tread it as such. Could the fact that I didn't treat the WHERE value as a string have done this?

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 5

P.S. Jeff - I agree with you on Michaels take on how to handle it moving forward (along with everyone's ideas).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

No.

The identity column would have to have been an int (can't set identity on a char column) so the value in the where clause would either have to be an int, or be converted to an int.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Jeff Moden (8/26/2011)
Michael Valentine Jones (8/26/2011)
This is a good illustration of why you should always run adhoc updates inside a transaction.
If you see the wrong number or rows updated, you can just roll it back. If everything looks OK, then commit it
-- Check expected number of rows to be updated
select count(*) from [dbo].
WHERE Column1 = 123
begin transaction
UPDATE [dbo].
SET ColumnA = X, ColumnB = Y
WHERE Column1 = 123
/*
rollback
commit
*/
THAT, good Sir, is the best recommendation, yet!

I actually preffer implicit transactions. Impossible to forget, mis-highlight.

The first line of code I write is ROLLBACK, then I start typing.

Saved my butt many times over. You forget to commit a select every once in a while, but you learn and get over that.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

I guess it's what you "cut your teeth on". I have a lot to hate with implicit transactions especially when a 3rd party vendor provides some code.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Jeff Moden (8/26/2011)
I guess it's what you "cut your teeth on". I have a lot to hate with implicit transactions especially when a 3rd party vendor provides some code.

What do you mean? I'm only talking about the dev phase of the code. It's begin tran for prod code!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Sorry, Remi... Didn't see where you made such a differentiation but, now that you've said that, I made no differentiation... I hate it in all environments after using it in Oracle for about 3 years. 😀

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Sure blame it on Oracle.

The one thing I like with it is that a tran is started now matter where you start executing the code.

That and the fact that you just can't forget or go around it by accident.