SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT TOP


SELECT TOP

Author
Message
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2519 Visits: 6232
sjimmo (12/29/2010)
WayneS
On my system, the last select starts with 328417.

Sorry Wayne,

On my system I get:
SELECT TOP (5) * FROM #Test WHERE RowID > 5000; - 5001
SELECT TOP (5) * FROM #Test WHERE RowID > 7000; - 7001
SELECT TOP (5) * FROM #Test WHERE RowID > 9000; - 9001
SELECT * FROM #Test; - 1


And on the one I just tested it on (SQL 2008 Express Edition) I got 283633. Interestingly, second time I got 915585--seems the outcome is largely random!
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15596 Visits: 11355
WayneS (12/29/2010)
Just to point out that this code:
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name like '#Customer%')
DROP TABLE #Customer



is NOT the proper way to test for the existence of a temporary table (create the temporary table in a different connection, and you'll get an error when you run this code!)

The proper way to test for the existence of a temporary table is:

IF OBJECT_ID('tempdb..#Customer') IS NOT NULL


Better still...?

IF OBJECT_ID(N'tempdb..#Customer', N'U') IS NOT NULL



(The original form would return an object id if there were a temporary procedure called #Customer)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15596 Visits: 11355
paul.knibbs (12/29/2010)
And on the one I just tested it on (SQL 2008 Express Edition) I got 283633. Interestingly, second time I got 915585--seems the outcome is largely random!

I assume you are both getting plans involving parallelism?
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2832 Visits: 605
The correct answer should be "it depends". As already stated, there are no guarantees even it seem to work in some cases, but with larger tables, parallell plans, many users and enterprise edition (advanced read ahead) you can't rely on the order of the result.

ALWAYS use the ORDER BY clause if you depend on the order.

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Konstantin Reu
Konstantin Reu
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2707 Visits: 783
hakan.winther (8/8/2011)
The correct answer should be "it depends". As already stated, there are no guarantees even it seem to work in some cases, but with larger tables, parallell plans, many users and enterprise edition (advanced read ahead) you can't rely on the order of the result.

ALWAYS use the ORDER BY clause if you depend on the order.



I think your opinion is little wrong.


Result of order depend on method to get result
1. Index seek
in this case relation engine use ROOT PAGE to start search. And result will be sort
Little trick for force seek use hint (ForceSEEK) and add search predicate in WHERE clause

2. Index scan
If index scan is used then relation engine get leaf level pages in order that is stored in Index Allocation Map This order may be any Wink and depend on free pages allocation at the moment of creation index


Please get DBCC IND, DBCC PAGE into your hands and verify this.

MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10912 Visits: 11980
neprosto (2/17/2012)
Result of order depend on method to get result
1. Index seek
in this case relation engine use ROOT PAGE to start search. And result will be sort
Little trick for force seek use hint (ForceSEEK) and add search predicate in WHERE clause


This is only partially true.
Since this is undocumented, there is no way to guarantee this even if it is true today. And while it is true today under most circumstances, it isn't always true. I don't have the time to repro this now, but I think that if the table is sufficiently large, then both partitioning the table and getting a parallel execution plan could change the order of rows.

2. Index scan
If index scan is used then relation engine get leaf level pages in order that is stored in Index Allocation Map This order may be any Wink and depend on free pages allocation at the moment of creation index


And this is definitely not correct. An IAM scan will only be used if the following conditions are met:
1) The query optimizer must specify an unordered scan - that is, the query optimizer is not interested in the order of the rows after the scan operator. This will not be the case if the optimizer has to guarantee some order (for an ORDER BY, GROUP BY, merge join operator, or other operator or query ingredient that requires ordered rows) and can spare a sort operator by scanning rows in order.
2) The query must be processed with either no locks at all or locks at the table level.
Since SQL Server defaults to row-level locks, IAM scans are very rare in practice; most index scans are still in index order - though, again, not guaranteed!

And also, on Enterprise Edition, Data Center Edition, and Developer Edition, the engine has the ability to do "piggy back" scans - if a scan is already in progress when a new (unordered) scan is required, it will start consuming rows from that scan already in process and then restart from the start until where it started piggy-backing.


Finally, please remember that none of the above behaviours are documented as "future proof". All of this may change - not only in a future version, but also in a service pack, CU or even in a hotfix. The only way to get results in an order you can depend upon is to use that ORDER BY clause. (And the extra benefit is that it also documents your code better). In cases where the normal access method would result in that order anyway, it won't even cost you anything extra, as the optimizer will not add an unneccessary sort step.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15596 Visits: 11355
Hugo Kornelis (2/17/2012)
Since this is undocumented, there is no way to guarantee this even if it is true today.

It isn't true and there are many ways to disprove it. This is a conveniently simple example:


USE tempdb
GO
CREATE TABLE dbo.Example
(
data integer NOT NULL,
padding character(8000) NOT NULL DEFAULT ''
);
GO
SET NOCOUNT ON;
GO
INSERT dbo.Example
(data)
SELECT v.number
FROM master.dbo.spt_values AS v
WHERE v.[type] = N'P'
AND v.number BETWEEN 1 AND 50;


CREATE UNIQUE INDEX c
ON dbo.Example (data);
GO
CHECKPOINT; DBCC DROPCLEANBUFFERS;
GO
SELECT TOP (10)
e.data, e.padding
FROM dbo.Example AS e WITH (FORCESEEK)
WHERE
e.data > 0;
GO
DROP TABLE dbo.Example;


Sample results:

Query plan:




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
Forum.jpg (55 views, 12.00 KB)
Forum.jpg (56 views, 24.00 KB)
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10912 Visits: 11980
SQL Kiwi (2/17/2012)
Hugo Kornelis (2/17/2012)
Since this is undocumented, there is no way to guarantee this even if it is true today.

It isn't true and there are many ways to disprove it. This is a conveniently simple example:

Wow! Thanks.

Can you explain what's going on? I played around a bit with the code. Adding an ORDER BY changes the results, of course - but the execution plan remains the same (or, if there are differences, they are hidden in a place where I haven't looked).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15596 Visits: 11355
Hugo Kornelis (2/17/2012)
Can you explain what's going on? I played around a bit with the code. Adding an ORDER BY changes the results, of course - but the execution plan remains the same (or, if there are differences, they are hidden in a place where I haven't looked).

Without an ORDER BY clause, the nested loops join has a WithUnorderedPrefetch attribute: pages needed for the RID lookup are fetched asynchronously (similar to read-ahead) and processed in whatever order the I/Os happen to complete (loosely speaking). With an ORDER BY clause, the loops join is required to preserve the order of its outer input, so the prefetch hint is WithOrderedPrefetch.

I should add that no-one should infer any guarantees about row ordering from the above information. The optimizer and query processor co-operate under different circumstances to ensure that any required guarantees are in fact honoured at run time. These guarantees are deep internal details, and are not visible to us in show plan (even the xml form). My comment about the loops join 'preserving' sort order on its outer input does *not* mean the 'seek result' is *always* ordered.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10912 Visits: 11980
SQL Kiwi (2/17/2012)
Without an ORDER BY clause, the nested loops join has a WithUnorderedPrefetch attribute: pages needed for the RID lookup are fetched asynchronously (similar to read-ahead) and processed in whatever order the I/Os happen to complete (loosely speaking). With an ORDER BY clause, the loops join is required to preserve the order of its outer input, so the prefetch hint is WithOrderedPrefetch.

I should add that no-one should infer any guarantees about row ordering from the above information. The optimizer and query processor co-operate under different circumstances to ensure that any required guarantees are in fact honoured at run time. These guarantees are deep internal details, and are not visible to us in show plan (even the xml form). My comment about the loops join 'preserving' sort order on its outer input does *not* mean the 'seek result' is *always* ordered.

Truly fascinating stuff. Thanks, Paul!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search