InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 23:37:04 GMT20http://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]Hugo Kornelis (2/17/2012)[/b][hr][quote][b]neprosto (2/17/2012)[/b][hr]Result of order depend on method to get result1. Index seek in this case relation engine use ROOT PAGE to start search. And result [b]will be sort[/b] Little trick for force seek use hint (ForceSEEK) and add search predicate in WHERE clause[/quote]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.[quote]2. Index scan If index scan is used then relation engine[b] get leaf level pages in order that is stored in Index Allocation Map[/b] This order may be any ;) and depend on free pages allocation at the moment of creation index[/quote]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.[/quote]Thank you for comment! First of all i agree, the index scan trough IAM page(s) occurs when table lock level is applied. [b]But I want to add NOLOCK/Dirty read also cause the same situation [/b]It force read leaf level page not in order that they are linked. I just check it via debug each page and links - it is still truth. But if I use fake predicate in where clause on any column that is in index, it forces engine use scan via root page and record appear in sorted order. It still valid when table is partitioned and plan is parallel. Just check on table with 6 section and six million of rows.I did not find any examples refute this. I know and remember - it is not documented, and there is not guaranties, but i think it very important to understand why records my appear in another order and how it order does. The key concept - SQL uses IAM pages to begin scan, not root page.PS Sorry for my English level ;)Mon, 16 Jul 2012 07:36:44 GMTKonstantin Reuhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]SQL Kiwi (2/17/2012)[/b][hr]Without an ORDER BY clause, the nested loops join has a With[b]Unordered[/b]Prefetch 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 With[b]Ordered[/b]Prefetch.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.[/quote]Truly fascinating stuff. Thanks, Paul!Fri, 17 Feb 2012 06:32:40 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]Hugo Kornelis (2/17/2012)[/b][hr]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).[/quote]Without an ORDER BY clause, the nested loops join has a With[b]Unordered[/b]Prefetch 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 With[b]Ordered[/b]Prefetch.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.Fri, 17 Feb 2012 06:22:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]Hugo Kornelis (2/17/2012)[/b][hr]Since this is undocumented, there is no way to guarantee this even if it is true today.[/quote]It isn't true and there are many ways to disprove it. This is a conveniently simple example:[/quote]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).Fri, 17 Feb 2012 05:59:21 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]Hugo Kornelis (2/17/2012)[/b][hr]Since this is undocumented, there is no way to guarantee this even if it is true today.[/quote]It isn't true and there are many ways to disprove it. This is a conveniently simple example:[code="sql"]USE tempdbGOCREATE TABLE dbo.Example( data integer NOT NULL, padding character(8000) NOT NULL DEFAULT '');GOSET NOCOUNT ON;GOINSERT dbo.Example (data) SELECT v.numberFROM master.dbo.spt_values AS vWHERE v.[type] = N'P'AND v.number BETWEEN 1 AND 50;[/code][code="sql"]CREATE UNIQUE INDEX cON dbo.Example (data);GOCHECKPOINT; DBCC DROPCLEANBUFFERS;GOSELECT TOP (10) e.data, e.paddingFROM dbo.Example AS e WITH (FORCESEEK)WHERE e.data > 0;GODROP TABLE dbo.Example;[/code]Sample results:[img]http://www.sqlservercentral.com/Forums/Attachment10883.aspx[/img]Query plan:[img]http://www.sqlservercentral.com/Forums/Attachment10884.aspx[/img]Fri, 17 Feb 2012 05:28:34 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]neprosto (2/17/2012)[/b][hr]Result of order depend on method to get result1. Index seek in this case relation engine use ROOT PAGE to start search. And result [b]will be sort[/b] Little trick for force seek use hint (ForceSEEK) and add search predicate in WHERE clause[/quote]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.[quote]2. Index scan If index scan is used then relation engine[b] get leaf level pages in order that is stored in Index Allocation Map[/b] This order may be any ;) and depend on free pages allocation at the moment of creation index[/quote]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.Fri, 17 Feb 2012 02:59:37 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]hakan.winther (8/8/2011)[/b][hr]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.[/quote]I think your opinion is little wrong.Result of order depend on method to get result1. Index seek in this case relation engine use ROOT PAGE to start search. And result [b]will be sort[/b] Little trick for force seek use hint (ForceSEEK) and add search predicate in WHERE clause2. Index scan If index scan is used then relation engine[b] get leaf level pages in order that is stored in Index Allocation Map[/b] 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.Fri, 17 Feb 2012 02:34:54 GMTKonstantin Reuhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxThe 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.Mon, 08 Aug 2011 07:14:28 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]paul.knibbs (12/29/2010)[/b][hr]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![/quote]I assume you are both getting plans involving parallelism?[url]http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx[/url]Wed, 29 Dec 2010 15:39:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]WayneS (12/29/2010)[/b][hr]Just to point out that this code:[code="sql"]IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name like '#Customer%')DROP TABLE #Customer[/code]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:[code="sql"]IF OBJECT_ID('tempdb..#Customer') IS NOT NULL[/code][/quote]Better still...?[code="sql"]IF OBJECT_ID(N'tempdb..#Customer', N'U') IS NOT NULL[/code](The original form would return an object id if there were a temporary procedure called #Customer)Wed, 29 Dec 2010 15:38:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]sjimmo (12/29/2010)[/b][hr][quote]WayneSOn my system, the last select starts with 328417. [/quote]Sorry Wayne,On my system I get:SELECT TOP (5) * FROM #Test WHERE RowID > 5000; - 5001SELECT TOP (5) * FROM #Test WHERE RowID > 7000; - 7001SELECT TOP (5) * FROM #Test WHERE RowID > 9000; - 9001SELECT * FROM #Test; - 1[/quote]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!Wed, 29 Dec 2010 06:38:28 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote]WayneSOn my system, the last select starts with 328417. [/quote]Sorry Wayne,On my system I get:SELECT TOP (5) * FROM #Test WHERE RowID > 5000; - 5001SELECT TOP (5) * FROM #Test WHERE RowID > 7000; - 7001SELECT TOP (5) * FROM #Test WHERE RowID > 9000; - 9001SELECT * FROM #Test; - 1Wed, 29 Dec 2010 06:27:38 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]ronmoses (12/21/2010)[/b][hr]For educational purposes, I would genuinely appreciate it if one of the folks who take issue with the "most of the time" factor could illustrate a scenario in which that script returns different results.Thanks!Ron[/quote]Does this suffice?[code="sql"]IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;CREATE TABLE #Test (RowID INT PRIMARY KEY CLUSTERED);;WITHTENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)INSERT INTO #TestSELECT N FROM TALLY;SELECT TOP (5) * FROM #Test WHERE RowID > 5000;SELECT TOP (5) * FROM #Test WHERE RowID > 7000;SELECT TOP (5) * FROM #Test WHERE RowID > 9000;SELECT * FROM #Test;IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;[/code]On my system, the last select starts with 328417.Wed, 29 Dec 2010 05:45:48 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxJust to point out that this code:[code="sql"]IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name like '#Customer%')DROP TABLE #Customer[/code]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:[code="sql"]IF OBJECT_ID('tempdb..#Customer') IS NOT NULL[/code]Wed, 29 Dec 2010 05:31:20 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]mtassin (12/21/2010)[/b][hr]These days I begin to wonder if the optimizer doesn't just return them in clustered index order when the ORDER BY clause is omitted (and a clustered index is present), and Microsoft lists in BOL that the order is arbitrary so that if they need to change the optimizer for some reason with a service pack, they can say "We told you it was arbitrary".[/quote]No, the optimizer (and storage engine) can choose whatever access path seems most efficient. For example:[code="sql"]CREATE TABLE dbo.Example ( row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED, data AS CONVERT(VARCHAR(11), row_id) PERSISTED NOT NULL UNIQUE );GO INSERT dbo.ExampleDEFAULT VALUES;GO 10SELECT E.row_id, E.dataFROM dbo.Example E;GODROP TABLE dbo.Example;[/code]Even on a simple scan of the clustered index, there are a number of ways to show that ordering isn't guaranteed. Using parallelism is the most popular one, but this works too:[code="sql"]CREATE TABLE dbo.Example ( data BIGINT NOT NULL, padding CHAR(5000) NOT NULL DEFAULT '' );GOCREATE CLUSTERED INDEX c ON dbo.Example (data)WITH (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF);GOINSERT dbo.Example (data) VALUES (CHECKSUM(NEWID()));GO 64-- Clustered index scan, but not in clustered index order!SELECT TOP (10) dataFROM dbo.ExampleGODROP TABLE dbo.Example;[/code]Tue, 28 Dec 2010 13:51:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]Hugo Kornelis (12/23/2010)[/b][hr]I got the question right, assuming that the submitter was unaware of the fact that there is no guarantee for this behaviour. I was then surprised to see in the explanation that he is in fact aware of this, and still marked Ann as the correct answer. I don't get this - basically, the explanation says that the correct answer is "undetermined", but that answer is not given.A weak question.[/quote]I have exactly the same opinion. Ann was the only reasonable option available, and I was expecting to have to mention that it wouldn't be guaranteed, but then he does it himself.Tue, 28 Dec 2010 10:56:29 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]mtassin (12/21/2010)[/b][hr][quote]Always use the top with an ORDER BY clause! [/quote]I got the question correct, but this is the last sentance of the answer's explanation.Why?Why should I always use TOP with an order by clause? What benefit do I get with say TOP 100% when I decide i want all the records to come back, just ordered?[/quote]I don't think anyone gave a direct answer to this (although someone else pointed out the same ambiguous wording.) The author meant you should always use top [i]with an order by clause.[/i] In other words, don't use top without an order by clause. The author was not saying you should always use top when you are using an order by clause.Mon, 27 Dec 2010 19:53:06 GMTRose Budhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxI got the question right, assuming that the submitter was unaware of the fact that there is no guarantee for this behaviour. I was then surprised to see in the explanation that he is in fact aware of this, and still marked Ann as the correct answer. I don't get this - basically, the explanation says that the correct answer is "undetermined", but that answer is not given.A weak question.[quote][b]ronmoses (12/21/2010)[/b][hr]For educational purposes, I would genuinely appreciate it if one of the folks who take issue with the "most of the time" factor could illustrate a scenario in which that script returns different results.Thanks!Ron[/quote]Read [url=http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx]this blog post[/url] I wrote a few years ago.For this specific scenario, without any changes to the code, the data, etc, I don't think [b]we[/b] can make this script return different results. But Microsoft can. Since they didn't document or otherwise guarantee this behaviour, future versions of SQL Server, or even service packs and hotfixes, may change this behaviour. Remember what happened to all those views that abused the pre-SQL2005 "TOP 100 PERCENT ... ORDER BY" behaviour? And what about GROUP BY without ORDER BY in SQL 6.5 and older?Thu, 23 Dec 2010 03:42:35 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]Ninja's_RGR'us (12/21/2010)[/b][hr]So which was better performance wise? maxdop 1 or 0?Yes I know the results are wrong without the order by![/quote]well for ad-hoc examination it was much better to leave off the order-by and remember that the results had sub-sections in nondeterministic order.otherwise the answer is the typical "it depends" - on tradeoff between sorting a huge flat resultset and the time it takes to select each part with multiple CPU.Wed, 22 Dec 2010 16:55:08 GMTMike Dougherty-384281http://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]dfine (12/22/2010)[/b][hr]Yes, the answer is "Ann". But when you say most of the time we would get “Ann” since the clustered index is created on “first name”. Why can’t we say it is always return the “Ann”? I did execute the query more than 10 times, all the time the result was “Ann”. Just trying to understand, in which scenario SQL Server return different result than “Ann”?[/quote]For this small result set with simple key it will probably always return 'Ann'.But when you are talking about very large tables with a clustered index, the output might not be predictable.Wed, 22 Dec 2010 05:06:13 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxYes, the answer is "Ann". But when you say most of the time we would get “Ann” since the clustered index is created on “first name”. Why can’t we say it is always return the “Ann”? I did execute the query more than 10 times, all the time the result was “Ann”. Just trying to understand, in which scenario SQL Server return different result than “Ann”?Wed, 22 Dec 2010 04:08:47 GMTdfinehttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]pavanr (12/21/2010)[/b][hr]I agree that without an ORDER BY clause, the returned result has no meaning.but can anyone explain, here in the case of Clustered Index, why it picks third record ?Does this depend on Order of Insertion ie., Identity values ?[/quote]When you create a clustered index on a table, the data in the table is logically re-ordered to match the index key. A side effect of that is that a SELECT against the data is likely to return data ordered according to the clustered index regardless of an ORDER BY clause, but this isn't guaranteed.Wed, 22 Dec 2010 00:50:51 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxThe PHYSICAL order of data is only guaranteed to match the clustered index just after it is created or rebuilt. In this case the index is created and the data immediately read, no inserts or updates are made to the table, so in this instance it is a fair assumption that 'Ann' will be returned first (and the only sensible assumption from the choices available).Nice discussion on this from the QOTD of october 5th[url]http://www.sqlservercentral.com/Forums/Topic998040-274-1.aspx[/url]Tue, 21 Dec 2010 19:15:52 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxhttp://msdn.microsoft.com/en-us/library/ms175132.aspxWe're all learning.:-)Tue, 21 Dec 2010 17:46:53 GMTdonvon40http://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]donvon40 (12/21/2010)[/b][hr]Adding a clustered index is equivalent to adding a primary key and doesn't determine order. I don't agree with this answer.[/quote]Categorically untrue. A primary key is a unique logical key. SQLServer happens to implement it by creating a physical index. By default this index is clustered and unique (as the PK is logically unique) but the index does NOT need to be clustered. It can be created as a non-clustered, unique index if you so choose. A clustered index can be created as a unique index but it does NOT need to be unique. You can create a non-unique, clustered index. Data will be ordered based on the clustered index if it exists, although as pointed out throughout this post, the use of "top" does not guarantee the order any longer. Data will not be ordered based on the PK unless the PK is implemented as a clustered index. Frequently the PK is in fact a poor choice for the clustered index as clustered indexes are well suited to "range searches", and people tend to implement an identity column as the PK rather than to use the "business key", but that is another debate in itself. However like most situations, it depends on your design and the queries being run as to what is the best choice of clustered index.The original statement above is therefore completely inaccurate. A clustered index is NOT equivalent to a primary key. RegardsRoddyTue, 21 Dec 2010 15:50:38 GMTRoddy.CAMERONhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxAdding a clustered index is equivalent to adding a primary key and doesn't determine order. I don't agree with this answer.Tue, 21 Dec 2010 10:36:54 GMTdonvon40http://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxI believe the ORDER BY concept should also apply to the explanation because it would make more sense to say "always include an Order By when using Top" instead of "Always use the top with an ORDER BY clause". However, neither case is "always" true. Overall I get the point but as a question with three distinct answer choices and no real distinct answer it seems flawed at best.Tue, 21 Dec 2010 09:51:39 GMTDavid Harderhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxJust to be the devils advocate...1 - this is a local temp table. So only this connection is going to read from it which eliminates joining another active read on the data.2 - there's only 1 page of data, so there's no way you'll get parralelism.3 - The clustered index is clearly supplied in the question.4 - This is a trivial plan, so the most likely plan is a clustered scan.5 - As far as I know, the data is ordered IN the page itself (not 100% sure).Yes I know it still depends, but there's not much more else that could screw with the current "correct" answer :w00t:.Tue, 21 Dec 2010 09:50:06 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxHi Steve,Can you please remove this question, because it is plain wrong:--Select the first customerSELECT TOP 1 * FROM #CustomerWithout ORDER BY, the order is not guaranteed.Thanks!AKTue, 21 Dec 2010 09:45:27 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]Dietmar Weickert (12/21/2010)[/b][hr]BOL ([url]http://msdn.microsoft.com/en-us/library/ms189463.aspx[/url]) states clearly [quote]If the query has no ORDER BY clause, the order of the rows is arbitrary. [/quote]hence putting "Ann" as the correct answer is simply wrong.Nevertheless, using TOP without an ORDER BY clause may definitely be useful: I like to use it to see just a sample row of a table not well known to me...;-)[/quote]It certainly seemed more arbitrary in the days of SQL 7 and SQL 2000, I remember trying to figure out what order records were coming back when I didn't use ORDER BY.These days I begin to wonder if the optimizer doesn't just return them in clustered index order when the ORDER BY clause is omitted (and a clustered index is present), and Microsoft lists in BOL that the order is arbitrary so that if they need to change the optimizer for some reason with a service pack, they can say "We told you it was arbitrary".Tue, 21 Dec 2010 09:08:30 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote]Always use the top with an ORDER BY clause! [/quote]I got the question correct, but this is the last sentance of the answer's explanation.Why?Why should I always use TOP with an order by clause? What benefit do I get with say TOP 100% when I decide i want all the records to come back, just ordered?Tue, 21 Dec 2010 09:05:46 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxBOL ([url]http://msdn.microsoft.com/en-us/library/ms189463.aspx[/url]) states clearly [quote]If the query has no ORDER BY clause, the order of the rows is arbitrary. [/quote]hence putting "Ann" as the correct answer is simply wrong.Nevertheless, using TOP without an ORDER BY clause may definitely be useful: I like to use it to see just a sample row of a table not well known to me...;-)Tue, 21 Dec 2010 08:47:27 GMTDietmar Weickerthttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxGood Question... I agree that not having an order by statement can cause the output to change. I also agree that the clustered index created in the script also had this affect.I also agree that this table schema would need a re-write for a production transactional database project. Thier are several articles in BOL and other places that would point that out.1. A table identity column should be part of the primary key for the table. 2. If a table has a clustered index, it should include the primary key.Tue, 21 Dec 2010 08:40:07 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]Mike Dougherty-384281 (12/21/2010)[/b][hr][quote][b]ronmoses (12/21/2010)[/b][hr]For educational purposes, I would genuinely appreciate it if one of the folks who take issue with the "most of the time" factor could illustrate a scenario in which that script returns different results.[/quote]I read through all the comments to be sure the "order by" was already.. noted. (as if there would be anything for me to add after the first 10 posts)I don't have time to make a reproducible illustration but wanted to throw in these two cents: With large data sets and parallelization you can get apparently unordered results even with the clustered index in effect due to the merge step simply combining multiple streams. Without an explicit order by clause, you get the unsorted merged streams. I discovered this trying to remove the cost of the order by under the erroneous assumption that the clustered index made the order by unnecessary. No, it's very necessary.[/quote]So which was better performance wise? maxdop 1 or 0?Yes I know the results are wrong without the order by!Tue, 21 Dec 2010 07:35:55 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspx[quote][b]ronmoses (12/21/2010)[/b][hr]For educational purposes, I would genuinely appreciate it if one of the folks who take issue with the "most of the time" factor could illustrate a scenario in which that script returns different results.[/quote]I read through all the comments to be sure the "order by" was already.. noted. (as if there would be anything for me to add after the first 10 posts)I don't have time to make a reproducible illustration but wanted to throw in these two cents: With large data sets and parallelization you can get apparently unordered results even with the clustered index in effect due to the merge step simply combining multiple streams. Without an explicit order by clause, you get the unsorted merged streams. I discovered this trying to remove the cost of the order by under the erroneous assumption that the clustered index made the order by unnecessary. No, it's very necessary.Tue, 21 Dec 2010 07:29:43 GMTMike Dougherty-384281http://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxNice question. If there was an extra option "It Depends", i would have selected this option as the correct answer.Tue, 21 Dec 2010 07:27:09 GMTSuriihttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxI agree that without an ORDER BY clause, the returned result has no meaning.but can anyone explain, here in the case of Clustered Index, why it picks third record ?Does this depend on Order of Insertion ie., Identity values ?Tue, 21 Dec 2010 07:13:21 GMTpavanrhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxThank you for the good question.Tue, 21 Dec 2010 06:50:52 GMTAbi Chapagaihttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxI also got it right but don't like the available answers. If MS and T-SQL experts emphasize that the [u]only[/u] reliable way to return a result set in a specified order is for the last (i.e., outer) SELECT to include an ORDER BY clause, why would we want to have a question like this?I like a straightforward QoD that teaches one idea. Unfortunately, this one teaches the wrong idea.RichTue, 21 Dec 2010 06:44:47 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1037478-2872-1.aspxGood question - but, I think the answers and explanation are lacking.The order of the clustered index is based upon the columns which make up the clustered index. (Change the field on the index to see this work.) The order is also based upon the collation of the database as well (simple enough to test, but add more data for a real test). Thus the TOP record returned is determined by these factors.Thus, without any other definition of the collation, I would have to agree with those calling for an It depends answer.Some good information can bee seen at http://msdn.microsoft.com/en-us/library/cc917672.aspxAdditional reading: http://msdn.microsoft.com/en-us/library/ms177443.aspxor http://searchsqlserver.techtarget.com/tip/Clustered-and-non-clustered-indexes-in-SQL-ServerThere are many other references out there as well.Tue, 21 Dec 2010 06:23:00 GMTsjimmo