With Nolock

Question

With Nolock

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: 150468 More actions · Answer 1

I do agree with Mr Guru there about the ORDER BY thing, but as a matter of curiosity, I think the following also guarantees a repeatable order:

WITH TS AS (SELECT * FROM dbo.Test TABLESAMPLE (100 PERCENT) REPEATABLE (91256))

SELECT TOP (10) * FROM TS;

Well, so long as no changes are made to the table anyway...:-)

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Paul White (10/26/2009)
I do agree with Mr Guru there about the ORDER BY thing, but as a matter of curiosity, I think the following also guarantees a repeatable order:
WITH TS AS (SELECT * FROM dbo.Test TABLESAMPLE (100 PERCENT) REPEATABLE (91256))
SELECT TOP (10) * FROM TS;
Well, so long as no changes are made to the table anyway...:-)

I believe that even that is subject to non-repeatability since there is no explicit order by (that I could see) in the query plan. At a minimum I think the following could lead to non-repeatability:

1) anytime the engine determines it can do an allocation-order scan (such as being under READ UNCOMMITTED isolation level)

2) partitioned table

3) merry-go-round scan

4) partitioned view

5) not sure about this one, but since BOL states "Rows on individual pages of the table are not correlated with other rows on the same page." I wonder if that could affect this outcome, despite it being 100 PERCENT sample.

Actually, I just did this type of query against a table with no clustered index and the plan was a table scan with ORDERED = FALSE. I think that at least implies the potential for non-repeatability.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

Yes I would think so too - I just haven't been able to make it behave the way we would expect yet.

It's currently sat in my interesting-but-useless pile. The only thing that interests me is how the server enforces the repeatability of the data set, and whether the mechanism used somehow also guarantees order. For example, does it keep a list of page IDs somewhere and always scan them in the same order, or does it make a temporary copy of the data?

The ordered:false in the query plan is relevant; I just wonder whether the storage engine does anything special for tablesample...

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 4

GSquared (10/22/2009)
The question to ask on that one is: If you were a manager, and had an employee who was really, really fast at his job, but who lied to you, broke things, and sabotaged the work of his co-workers, would you keep him around? If the answer is yes, then with(nolock) is for you.

You say that like it's a bad thing...:-P

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."