Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

SELECT TOP Expand / Collapse
Author
Message
Posted Wednesday, December 29, 2010 6:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:12 AM
Points: 1,610, Visits: 5,482
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!
Post #1040236
Posted Wednesday, December 29, 2010 3:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 11,192, Visits: 11,095
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1040619
Posted Wednesday, December 29, 2010 3:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 11,192, Visits: 11,095
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1040620
Posted Monday, August 8, 2011 7:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:45 AM
Points: 2,531, Visits: 536
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
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
Post #1156024
Posted Friday, February 17, 2012 2:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 12, 2014 12:18 AM
Points: 2,063, Visits: 519
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 ;) and depend on free pages allocation at the moment of creation index


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


MCITP x 3
MCSE x 1
Post #1253685
Posted Friday, February 17, 2012 2:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 5,925, Visits: 8,173
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 ;) 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
Post #1253698
Posted Friday, February 17, 2012 5:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 11,192, Visits: 11,095
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Forum.jpg (45 views, 12.07 KB)
Forum.jpg (45 views, 24.88 KB)
Post #1253774
Posted Friday, February 17, 2012 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 5,925, Visits: 8,173
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
Post #1253794
Posted Friday, February 17, 2012 6:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 11,192, Visits: 11,095
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1253801
Posted Friday, February 17, 2012 6:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 5,925, Visits: 8,173
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
Post #1253809
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse