Re: Is bookmark in a nonclustered index ordered?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Paul S Randal [MS] (prandal_at_online.microsoft.com)
Date: 02/05/05 

Date: Sat, 5 Feb 2005 10:36:23 -0800

Here's an example of why you cannot rely on the order of rows returned
unless you specifically use an ORDER BY. We have a concept of merry-go-round
scans. If two queries need to perform a range scan over an index, they can
share the same scan. Now, if query one starts before query two, we still
recognize that the scan we need for query two is already under way and we
piggy-back on it until it completes. However, because we hopped on it after
it had started, we need to go back to the start of the scan to read the rows
we missed. (e.g. query one scans and returns rows 1 to 100. Query two starts
when query one's scan is at row 46. Query two thus returns rows 46 to 100
plus 1 to 45)

Regards. 

-- 
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:WvidncyWk-E2CZnfRVn-hg@giganews.com...
> An index (clustered or non-clustered) shouldn't be relied upon to fix the
> order in which rows are returned. There are no guarantees unless you use
> ORDER BY in your query. Use ORDER BY and let the server decide which index
> is most appropriate.
>
> -- 
> David Portas
> SQL Server MVP
> --
>
>

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)