SQL SERVER – A Tricky Question and Even Trickier Answer – Index Intersection – Partition Function

During yesterday’s evening, I asked a very simple question on my Facebook Page. The question was written in a jiffy and in a very light mood. While writing the question, I left a few things out, and the question did miss a few details about setup. However, as the question was not complete, it created an extremely interesting conversation in the following thread.

Here is the question: Write a select statement using a single table, using single table single time only without using join keywords, which generate execution plan with 2 join operators. Use AdventureWorks as a sample database.

I got many interesting answers to the question I posted. I must say that I learned something new from all these answers. Before I discuss my own answer, let me show the answer provided by Alphonso Jones.

Here is his answer:

INTO #tmp
FROM sys.columns
-- Enable Execution Plan with CTRL+M
SELECT num, SUM(num2) OVER (Partition BY num)
FROM #tmp

When I saw this answer – I was very happy because I did not visualize it as a solution when I was asking the question. Here is the execution plan of the T-SQL code above. It’s easy to see that there are multiple joins because of the Partition Function used in the query. What an excellent participation by Alphonso Jones.

Click to Enlarge

Here is the answer which I had visualized when I asked the question. I was running the query on AdventureWorks database and executed the following query, which in turn, generated an execution plan with multiple joins:

USE AdventureWorks2012
FROM [Purchasing].[PurchaseOrderHeader]
WHERE [EmployeeID] = 258 AND [VendorID] = 1580

Look at the execution plan of the above query. You can see the joins even though I am using single table and there is no join syntax in the query.

Click to Enlarge

Personally, I liked the solution of Alphonso Jones as his solution will always generate multiple joins due to Partition Function. On the other hand, my solution is a bit tricky for it requires Indexes on the table [Purchasing].[PurchaseOrderHeader], which generates index intersection. Index Intersection is a technique which utilizes more than one index on a table to satisfy a given query.

Thanks Alphonso Jones.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Scripts
Previous Post
SQL SERVER – Video – Step by Step Installation of SQL Server 2012
Next Post
SQL SERVER – Identify Columnstore Index Usage from Execution Plan

Related Posts

1 Comment. Leave new

  • Simran Jindal
    May 3, 2012 1:59 pm

    Great post Pinal. I have been following the discussion on facebook.Alphonso’s query definitely generates predictable results.

    ” Index Intersection” would be interesting for some future posts.

    Thanks for your dedicated writing.


Leave a Reply Cancel reply

Exit mobile version