DaveWentzel.com            All Things Data

Scans and Sorts


Scans Vs Sorts...which is better? (Here is the hierarchy)

--Table scan or index scan if table is small
--Index seek or index scan
--Table scan or clustered index scan
Sort Operator
--Sorts all incoming rows
--Argument column will show ORDER BY:() or DISTINCT ORDER BY:() predicate if sort is used to remove duplicate rows
--Performance Ramifications:
     --Avoid these during intermediate operations
     --if there are multiple sort operators, you need to determine why...they can likely be eliminated through refactoring. 
     --Unavoidable if you use ORDER BY or (usually) GROUP BY in your statement
     --Covering indexes and clustered indexes
--Profiler can be configured to show Sort Warnings when they are generated.  A sort warning occurs when a sort is done on disk vs in RAM.