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:

SELECT Row_number() OVER (ORDER BY OBJECT_ID) num, Rank() OVER (ORDER BY OBJECT_ID DESC) num2
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.

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
GO
SELECT *
FROM [Purchasing].[PurchaseOrderHeader]
WHERE [EmployeeID] = 258 AND [VendorID] = 1580
GO

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.

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)