When the query plan hash is deceptive

When you’re querying the plan cache, you need to know the four primary objects: the query hash, query plan hash, sql handle, and plan handle. However, the query plan hash recently surprised me.

Take a look at this index (in StackOverflow2010)

CREATE NONCLUSTERED INDEX [ix_Location_CreationDate] ON [dbo].[Users]
       ([Location] ASC,
	[CreationDate] ASC)
INCLUDE ( [Reputation])

Okay, we have a nonclustered index. Now I want a query that uses this index, but also does a key lookup to the clustered index, looking for the UpVotes column.

SELECT 
	Reputation
	,UpVotes
  FROM [StackOverflow2010].[dbo].[Users]
  WHERE Location = 'United States'
   and CreationDate >= '2009-05-01' 
   and CreationDate <= '2009-05-10'

Here’s the execution plan.

Now, let’s change the UpVotes column to EmailHash

SELECT 
	Reputation
	,EmailHash
  FROM [StackOverflow2010].[dbo].[Users]
  WHERE Location = 'United States'
   and CreationDate >= '2009-05-01' 
   and CreationDate <= '2009-05-10'

The new query plan hash is 0xD30D4B58F7A1E0A8, exactly the same as the first plan hash.

Surprised?

I was surprised at first. I expected a different query plan hash because there was a different column being returned. I think it makes sense though. The query plan hash is supposed to represent the structure of the plan, and in both examples, it’s the same indexes used for the seek, key lookup, and nested loop operators.

One bonus example

What if we add the UpVotes column to the WHERE clause instead of the SELECT clause?

SELECT 
	Reputation
  FROM [StackOverflow2010].[dbo].[Users]
  WHERE Location = 'United States'
   and CreationDate >= '2009-05-01' 
   and CreationDate <= '2009-05-10'
   and UpVotes = 50

That’s the same query plan hash, even when there’s a new predicate in the WHERE clause.

I hope this was interesting! If you take a look at the query hashes (apologies for the size of the screenshot), they changed for each different query. Stay tuned!