With my friend Mark, we recently discussed how the SQL server can deal with data retrieval, if the data are sorted by index in specified direction and query requests data in the opposite direction. As you can see below SQL server optimizer can deal with this request without the sort operator, which brings less costs in query execution. But it is very important that query meets with index design, otherwise sort operator occurs in query plan.
From samples bellow it is clear how it is important to pay attention to query with comparation to index design to avoid Sort operator
Let’s create a simple table with non-clustered index – ordered in ASC direction.
CREATE TABLE dbo.TestTable(id INT,sec_no INT)
CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable (sec_no ASC)
Let’s fill it with data.
INSERT INTO dbo.TestTable SELECT object_id,s.schema_id FROM sys.objects s
Now let’s look at execution plans of queries bellow. The first one ordered in ASC direction and the second one in DESC.
SELECT sec_no FROM dbo.TestTable ORDER BY sec_no ASC SELECT sec_no FROM dbo.TestTable ORDER BY sec_no DESC
As you can see on both execution plans there are scans of non-clustered index and no sort operators.
If we look closer on Index Scan operators F4 we can see that optimizer used Scan Direction Forward on the first query execution plan and Backward in plan with DESC ordering.
Forward scan direction
Backward scan direction
Now if we remove non-clustered index we and run above mentioned queries we can see that Sort operator appears in our execution plans.
DROP INDEX IX__TestTable__sec_No ON dbo.TestTable
Create non-clustered index again and change little bit its design. Now the data are ordered by ID column first.
CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable (id,sec_no)
Now query optimizer uses index to retrieve data again but because index design does not correspond with our query (ordering of query does not fit to index), data has to be ordered in Sort operator. If we look by F4 on Index scan operator we can see that data are not returned in requested order – Ordered attribute is set to false.
We can get similar situation if we go back to our first example with right index design.
DROP INDEX IX__TestTable__sec_No ON dbo.TestTable CREATE NONCLUSTERED INDEX IX__TestTable__sec_No ON dbo.TestTable(sec_no ASC)
But now we change our query little bit.
SELECT sec_no,id FROM dbo.TestTable ORDER BY sec_no ASC
From picture we can see that execution plan is similar like the one where we removed index. Here index exist on table but cannot be used to retrieve data because we changed our query the way index cannot be used and sort operator occurs again.
Lets create second table with index designed in right way as our first examples states. And compare both execution plans.
SELECT * INTO dbo.TestTableII FROM dbo.TestTable CREATE NONCLUSTERED INDEX IX__TestTableII__sec_No ON dbo.TestTableII (sec_no)
From picture above, we can see that sort operator is a resource consuming operation from the perspective of query optimizer. In case you see it in the execution plan it is necessary to pay attention to it. If we want to avoid sorting in the execution plan, a suitable index design matching our query can help. However, this is not all yet. In the next posts I want to look at the Sort operator in more detail. And we will show that in some cases it also depends on the ordering direction of the index.