Index vs Sort Operator, how we can remove sort operator from execution plan

Here in this article we will see how we sort operator is performing in a challenge with index scanning operator, lets create platform for executing this test.

Here we are creating table Tab_EmployeeAvoidingSort which has no index defined on any column.

set nocount on

if object_id('Tab_EmployeeAvoidingSort') is not null

drop table Tab_EmployeeAvoidingSort

go

create table Tab_EmployeeAvoidingSort

(id int identity(1,1),

name varchar(30),

salary numeric(18,2));

go

Lets insert some set of data to the table, say 1000

declare @i smallint

set @i = 0

while @i < 1000

begin

insert into Tab_EmployeeAvoidingSort(name, salary)

select 'Employee', abs(convert(numeric(18,2), (checksum(newid()) / 500000.0)))

set @i = @i + 1

end

Our table is ready to do the test on sort operation. Lets on the Statistic profiler to track the IO and CPU and total cost taken by query involving sort operator.

SET STATISTICS PROFILE ON

Select name from Tab_EmployeeAvoidingSort order by name

Here we can see the cost for execution involving the sort operator came out to 0.0035.

Lets create index on name column on name column.

create nonclustered index ix_name on Tab_EmployeeAvoidingSort(name)

go

Command(s) completed successfully.

Index is all set, lets try out the same query to see what cost it involves

SET STATISTICS PROFILE ON

select name from Tab_EmployeeAvoidingSort with (index = ix_name) order by name

Oh!!! Great, total cost of query comes out as 0.0066, which is much lesser then the one involving sort operator.

  

Conclusion: Try to implement index on order by column.

Get your tea and start your work :)

Post Reference: Vikram Aristocratic Elfin Share