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:

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

http://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:

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

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 :)