When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False. I will mention that we have parallelism involved, though it does not appear to have any relation to these. The indexes were set up with ordering.

What does this mean? That the query plan is ignoring this order? Or that the query is choosing not to order? Or... heck, I have no idea what I'm looking at...!


It just means that the query optimiser doesn't require an explicit order guarantee either for some later operator in the plan (e.g. merge join or stream aggregate) or to avoid a sort because you have explicitly requested an order by.

When ordered = false you might in some circumstances get an allocation ordered scan rather than a scan that follows the linked list of the leaf pages in index key order for example.

  • Since the scan type: index order or allocation order does not appear in the execution plan or elsewhere, what to tell people with questions such as this one? Basically, one can trade scan speed for data safety, no? I guess I would say, maybe consider forcing allocation order scans when doing big scans on tables, which are not changing at the time of the scan...thinking DWs. Thoughts? – ooutwire Mar 13 '12 at 14:36
  • @ooutwire - In a DW if the table is on a read only file group wonder if you might get an allocation ordered scan considered automatically without having to use nolock or tablock. Would make sense but haven't tested this. – Martin Smith Mar 13 '12 at 14:40
  • I agree, but the DWs I use are never read-only (unless we are mucking with them surreptitiously). I suppose if little logical fragmentation exists in the first place and nothing is going on to cause page splits, then one may see little gain from forcing the allocation scan. Guess it'll make a good blog post. I have some TB-sized DWs I can attack for fun. – ooutwire Mar 13 '12 at 14:55
  • Makes good sense, Martin. Excellent link you shared as well. Thanks much! – JHFB Mar 13 '12 at 16:19

I believe the Ordered portion of an index scan is just indicative of whether or not your query is ordered by the index key column(s).

Say you have something like this:

-- Table Definition for reference
CREATE TABLE [HumanResources].[Employee](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [NationalIDNumber] [nvarchar](15) NOT NULL,
    [ContactID] [int] NOT NULL,
    [LoginID] [nvarchar](256) NOT NULL,
    [ManagerID] [int] NULL,
    [Title] [nvarchar](50) NOT NULL,
    [BirthDate] [datetime] NOT NULL,
    [MaritalStatus] [nchar](1) NOT NULL,
    [Gender] [nchar](1) NOT NULL,
    [HireDate] [datetime] NOT NULL,
    [SalariedFlag] [dbo].[Flag] NOT NULL,
    [VacationHours] [smallint] NOT NULL,
    [SickLeaveHours] [smallint] NOT NULL,
    [CurrentFlag] [dbo].[Flag] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [EmployeeID] ASC


use AdventureWorks

select *
from HumanResources.Employee

This would be a clustered index scan with Ordered = False. But modify it to look like this:

use AdventureWorks

select *
from HumanResources.Employee
order by EmployeeID

You will then have Ordered = True because you are ordering by the index key column.

But if you have something like this (ORDER BY on a non-key column):

use AdventureWorks

select *
from HumanResources.Employee
order by ContactID

Then Ordered = False because the sorting isn't based on the index key column(s).


A problem (and solution) I just hit regarding Ordered = False:

I had the following operators in my Execution Plan:

  • a Sort, spilling to tempdb with 1.7 GB of data, which got its data from
  • a Compute Scalar, a bunch of columns getting Explicit (but also Implicit!) conversions
  • ...which came from what should have been an Ordered = True Clustered Index Scan.

My Clustered Index scan was getting the Ordered = False treatment, because one of the implicit conversions was for one of the Index's Key Columns!

Once the implicit conversion on that key column was remedied, the plan changed to a much faster:

  • Compute Scalar, columns getting Explicit and Implicit conversions, but not the Clustered Index Key Columns!
  • Clustered Index Scan with Ordered = True

This avoided the Sort and the spill to TempDB completely!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.