SQL Server Index Internals Part 3 – Index union operations

Hi Friends,

I am writing this blog on my way back from Great Indian Developer Summit where I presented 2 sessions and received great feedback on my session. I had started the SQL Server Index Internals series sometime back and posted two blogs. You can find them here:

https://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/487/sql-server-index-internals-part-1-%E2%80%93dynamic-index-seek-operation

https://www.sqlservergeeks.com/sql-server-index-internals-part-2-why-is-the-optimizer-seeking-instead-of-scanning/

This is the third one in the series and today I will talk about Index Union operations.

Lets create two non-clustered indexes on SalesOrderHeader table in AdventureWorks database:

USE AdventureWorks
GO
 
CREATEINDEX idx_nc_OrderDate on sales.SalesOrderHeader(OrderDate)
CREATEINDEX idx_nc_ShipDate on sales.SalesOrderHeader(ShipDate)

And execute the following query (turn on actual execution plan):

   
SELECT SalesOrderId
FROM sales.SalesOrderHeader
WHERE OrderDate BETWEEN'2002-01-01'AND'2002-01-07'
OR ShipDate BETWEEN'2002-01-01'AND'2002-01-07'

After observing the above query, you will notice why I created two non-clustered indexes on OrderDate&ShipDate respectively. Now let us see the execution plan for the above query and analyze what the optimizer has done:

1_SQL_Server_Index_Internals_Part3_Index_union_operations

The optimizer has basically used both the indexes since we were using those columns in our query predicates. And it joins the data (concatenation operator). This kind of operation is called as Index union.

So here is a question for you; what is the purpose of Distinct Sort operator? Post your answers as comments 🙂 – And yes, I will respond to each comment (I know I have been late in responding and still some responses are pending. I will clear my back log quickly 🙂

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

3 Comments on “SQL Server Index Internals Part 3 – Index union operations”

  1. My guess is that because you concatenate 2 results that come from 2 differents indexes involve a high potential of duplicate results of SalesOrderId that comes from these 2 indexes. SQL needs to remove those duplicates to conform to expected results of the query (Only 1 table is used in the query that sould not produce duplicates if SalesOrderId is unique). The most effective way to eliminate duplicates is sorting them and removing multiple consecutive identical results.

    My Question is the following:

    – What happens if SalesOrderId is not Unique in the table? How SQL Server can identify duplicates that comes from the scan of 2 indexes from the real duplicates in the table (that must be returned).

    – The execution plan will probably looks different, if you could provide an example and explain the new one it could be great

    Thanks for this explanation, learning a little thing each day make better DBA.

  2. Hi Amit

    How can i tune or create index on below query if employee table is huge.

    select * from employee where last_name like ‘%A’

  3. It says DISTINCT sort. After merging data selected from 2 unrelated indexes some of which can refer to the same records, it needs to pick distinct records only.

Leave a Reply

Your email address will not be published.