SELECT [columns] with ORDER BY - trying to understand why  
Author Message
JamieL





PostPosted: Mon Aug 13 14:04:51 CDT 2007 Top

SQL Server Developer >> SELECT [columns] with ORDER BY - trying to understand why

When MSSQL is given a query such as
select * from mytable order by col1, col2
on a table with more columns than col1 and col2, and having an index on
col1, col2 an execution plan says a sort will be performed.

However, given the query
select col1, col2 from mytable order by col1, col2
the execution plan says no sort is required and an index seek will be
performed.

My question is why does MSSQL require the sort when more columns are
requested in the result set? The request is to order only by col1, col2, so
the other columns returned should not require sorting I would have thought,
thus allowing an index seek to be performed as well.

I am just trying to understand the logic behind the operation...
The table has no clustered index...

SQL Server119  
 
 
Tibor





PostPosted: Mon Aug 13 14:04:51 CDT 2007 Top

SQL Server Developer >> SELECT [columns] with ORDER BY - trying to understand why Most probably in the second case you have an index on col1, col2, and that index covers the query.
Google on "covering indexes" and "covered queries" to read more about the concept. Basically all the
data needed for the query is in the index, which already is sorted. But when you add more columns,
not all are in the index and scanning the table and then sorting is considered cheaper than using
the index *and for each row* to the lookup to fetch the other column values.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi




> When MSSQL is given a query such as
> select * from mytable order by col1, col2
> on a table with more columns than col1 and col2, and having an index on
> col1, col2 an execution plan says a sort will be performed.
>
> However, given the query
> select col1, col2 from mytable order by col1, col2
> the execution plan says no sort is required and an index seek will be
> performed.
>
> My question is why does MSSQL require the sort when more columns are
> requested in the result set? The request is to order only by col1, col2, so
> the other columns returned should not require sorting I would have thought,
> thus allowing an index seek to be performed as well.
>
> I am just trying to understand the logic behind the operation...
> The table has no clustered index...
>
>

 
 
Roy





PostPosted: Mon Aug 13 14:08:34 CDT 2007 Top

SQL Server Developer >> SELECT [columns] with ORDER BY - trying to understand why When the query is only on the indexed columns, the non-clustered index
"covers" the query, which is to say all the data required can be found
in the index, and the base table need not be read. Since the index is
already in order, no sorting is required.

When non-indexed columns are included in the query the base table must
be read. Unless there is a WHERE clause test to justify using the
index, it is more efficient for SQL Server to simply scan the entire
table and sort. the reason it is more efficient is that it only has
to read each page once. Traversing the non-clustered index would be
inefficient because it would have to perform a logical page read for
almost every row, much more expensive than the sort it would save.

Roy Harvey
Beacon Falls, CT

On Mon, 13 Aug 2007 11:48:03 -0700, Geoff


>When MSSQL is given a query such as
>select * from mytable order by col1, col2
>on a table with more columns than col1 and col2, and having an index on
>col1, col2 an execution plan says a sort will be performed.
>
>However, given the query
>select col1, col2 from mytable order by col1, col2
>the execution plan says no sort is required and an index seek will be
>performed.
>
>My question is why does MSSQL require the sort when more columns are
>requested in the result set? The request is to order only by col1, col2, so
>the other columns returned should not require sorting I would have thought,
>thus allowing an index seek to be performed as well.
>
>I am just trying to understand the logic behind the operation...
>The table has no clustered index...
>