Scan Direction

Last Post 08 Jul 2012 12:10 AM by rerichards. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
rerichards
New Member
New Member

--
06 Jul 2012 07:34 AM
In running this statement on my box, the values of x returned by the Top 10 are 3 through 19. Yet, the max(x) being returned is 2107154552.

WITH MyCTE(x)
as
(
select top 10 x = id from sysobjects where id > 0
)
select x from MyCTE
union all
select max(x) from MyCTE

Likewise, the same is happening when the statement is being run outside the CTE, the max(x) returned is 2107154552.

select max(x)
from (
select top 10 x = id from sysobjects where id > 0
) dt

In each instance the top 10 statement uses a clustered index seek, and the properties of the clustered index seek have:
ORDERED = TRUE
SCAN DIRECTION = FORWARD

This, to my understanding translates into ORDER BY id ASC.

Why then, in obtaining the max, does the SCAN DIRECTION change to BACKWARD (Descending)?
gunneyk
New Member
New Member

--
07 Jul 2012 06:02 PM
A TOP without an ORDER BY clause is useless unless you don't care which rows are returned. Add an order by to the select with the TOP so that you have something to base the results on. And never rely on the order of rows returned from a query even if it uses a clustered index unless you have an ORDER BY clause. SQL Server is free to return rows in any order it wants if there is no ORDER BY specified and a clustered index does not guarentee any order without it even though many times it may seem that way.
rerichards
New Member
New Member

--
08 Jul 2012 12:10 AM
Thanks gunneyk. That makes sense.


Acceptable Use Policy
---