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.

SQL SERVER - A Tricky Question and Even Trickier Answer - Index Intersection - Partition Function exjoin1
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
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.

SQL SERVER - A Tricky Question and Even Trickier Answer - Index Intersection - Partition Function exjoin2
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.

    Reply

Leave a Reply