Index and Sort Order

Introduction

When we write the SQL Query we frequently use the ORDER BY clause to get the output in definite order (Ascending or descending).

But ORDER BY clause has deep impacts in the performance of the query executions. In this article I am trying to discuss related to it.

How it Works

When designing the index we should consider whether the data for the index key column should be stored in ascending or descending order. Ascending is the default order and maintains compatibility with earlier versions of SQL Server.  It is most logical one, that the smallest values in the top and the biggest one is the bottoms.

Bust everything depends on the situations or the way we represent the records or how we constructs the SQL statements.

To understand it properly, I am taking an simple example where I am trying to demonstrate the performance issue with index data ordering.

Step-1 [ First create a Base Table ]

Here in this example I am taking my favorite employee table objects with attributes employee id, employee name and the department.

 -- Base Table

CREATE TABLE my_Emp

       (EmpID     INT NOT NULL IDENTITY(1,1),

        EmpName   VARCHAR(50) NOT NULL,

        EmpDept   VARCHAR(1)  NOT NULL)

Step-2 [ Now Insert some records on my base table ]

-- Record Insertion

INSERT INTO my_Emp

       (EmpName,  EmpDept)

VALUES ('Joydeep Das', 'A'),

       ('Tuhin Shinah', 'B'),

       ('Sangram Jit', 'C'),

       ('Sukamal Jana', 'A'),

       ('Sudip Das', 'B'),

       ('Manishankar Bhowmik', 'C'),

       ('Ratan Das', 'A'),

       ('Sidhu Jetha', 'B'),

       ('Subrata Kar', 'C')  

GO

Step-3 [ Now I am creating a non clustered index on table  ]

-- Index Created 

DROP INDEX ix_nonclus_my_Emp ON my_Emp    

CREATE NONCLUSTERED INDEX ix_nonclus_my_Emp

ON  my_Emp (EmpID, EmpName)

Step-4 [ Now execute the following Query and find the Actual Execution Plan ]

SELECT   EmpID, EmpName

FROM     my_Emp WITH(INDEX(ix_nonclus_my_Emp))

ORDER BY EmpID ASC, EmpName DESC

Step-5 [ Now Drop the Index and Recreate it again by using ASC and DESC clause]

-- Drop Index

DROP INDEX ix_nonclus_my_Emp ON my_Emp

GO

-- Recreate Index

CREATE NONCLUSTERED INDEX ix_nonclus_my_Emp

ON  my_Emp (EmpID ASC, EmpName DESC)


GO

Step-6 [ Now execute the following Query and find the Actual Execution Plan ]

SELECT   EmpID, EmpName

FROM     my_Emp WITH(INDEX(ix_nonclus_my_Emp))

ORDER BY EmpID ASC, EmpName DESC

Step-7 [ Now Compare Both the Execution Plan ]

In this case the SORT operator is found in the first execution plan but not in second hence increase the performance.

Summary

Sort order can be specified only for key columns. The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

Hope you like it.

Posted by: MR. JOYDEEP DAS