DateDiff on Consecutive Rows

Question

DateDiff on Consecutive Rows

Viewing 4 posts - 16 through 18 (of 18 total)

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

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

Wildcat (12/20/2010)
How could a NOLOCK hint to the target table on the DELETE or UPDATE statement? Make no sense to me.

Precisely. It's just an oversight in the parser:

-- Error 1065:

-- The NOLOCK and READUNCOMMITTED lock hints are not allowed for

-- target tables of INSERT, UPDATE, DELETE or MERGE statements.

UPDATE dbo.Test WITH (NOLOCK) SET B = 2 WHERE B = 1

-- No error, but no effect either

UPDATE dbo.Test SET B = 2 FROM dbo.Test WITH (NOLOCK) WHERE B = 1;

UPDATE T SET B = 2 FROM dbo.Test T WITH (NOLOCK) WHERE B = 1;

The second two forms do not raise an error, which has mislead people into believing that it somehow behaves differently.

This is a myth. The NOLOCK/READCOMMITTED hint has absolutely no effect here.

SQL Server takes (at least) Update locks when scanning for rows to update, regardless of isolation level.

It's the syntax that is deprecated: the behaviour is unchanged.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

SQLkiwi (12/20/2010)
Wildcat (12/20/2010)
How could a NOLOCK hint to the target table on the DELETE or UPDATE statement? Make no sense to me.
Precisely. It's just an oversight in the parser:
-- Error 1065:
-- The NOLOCK and READUNCOMMITTED lock hints are not allowed for
-- target tables of INSERT, UPDATE, DELETE or MERGE statements.
UPDATE dbo.Test WITH (NOLOCK) SET B = 2 WHERE B = 1
-- No error, but no effect either
UPDATE dbo.Test SET B = 2 FROM dbo.Test WITH (NOLOCK) WHERE B = 1;
UPDATE T SET B = 2 FROM dbo.Test T WITH (NOLOCK) WHERE B = 1;
The second two forms do not raise an error, which has mislead people into believing that it somehow behaves differently.
This is a myth. The NOLOCK/READCOMMITTED hint has absolutely no effect here.
SQL Server takes (at least) Update locks when scanning for rows to update, regardless of isolation level.
It's the syntax that is deprecated: the behaviour is unchanged.

Agh. My bad. I didn't read that they were trying to do it on an UPDATE or anything other than a SELECT for that matter.

--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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 3

Jeff Moden (12/21/2010)
Agh. My bad. I didn't read that they were trying to do it on an UPDATE or anything other than a SELECT for that matter.

No worries 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

SQLkiwi (12/20/2010)
Wildcat (12/20/2010)
How could a NOLOCK hint to the target table on the DELETE or UPDATE statement? Make no sense to me.
Precisely. It's just an oversight in the parser:
-- Error 1065:
-- The NOLOCK and READUNCOMMITTED lock hints are not allowed for
-- target tables of INSERT, UPDATE, DELETE or MERGE statements.
UPDATE dbo.Test WITH (NOLOCK) SET B = 2 WHERE B = 1
-- No error, but no effect either
UPDATE dbo.Test SET B = 2 FROM dbo.Test WITH (NOLOCK) WHERE B = 1;
UPDATE T SET B = 2 FROM dbo.Test T WITH (NOLOCK) WHERE B = 1;
The second two forms do not raise an error, which has mislead people into believing that it somehow behaves differently.
This is a myth. The NOLOCK/READCOMMITTED hint has absolutely no effect here.
SQL Server takes (at least) Update locks when scanning for rows to update, regardless of isolation level.
It's the syntax that is deprecated: the behaviour is unchanged.

Good catch - I read right over the word target. I only use the FROM clause in a "joined update", where nolock can cause problems (when applied to related tables).

Thanks!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?