July 3, 2013

Takeaway: I tackled a problem recently and I present a version of the problem here as a challenge. The challenge is to figure out why a query and a stored procedure return different results even though the procedure is defined using the same query.

If no one cracks this one, I’ll leave the answer in the comments in a couple days.

The Setup

Okay, I use AdventureWorks2012 here. I define a table data type IDList and a stored procedure s_GetCustomerProductPurchases:

So Why Are These Results Different?

That’s what I want you to figure out. Try to crack this one before looking at the comments.

SQL Fiddle

Have you used SQL Fiddle yet? It’s a site that allows SQL developers to set up different scenarios for use in online discussions. So if you don’t have Adventureworks2012 handy, I’ve setup up two SQLFiddles here and here. Your job then is to follow these two links and explain why the results are different.

Aaron Bertrand (b|t) pointed me to a post (First steps into Hekaton) by Dave Ballantyne (b|t) who suggests that in SQL Server 2014 (Hekaton), when you pass parameters to a natively compiled stored procedure, the named parameters might be slow. This guess is based on a mysterious new extended event: “hekaton_slow_parameter_passing”. Go read Dave’s article.

it’s a very nice post solution was so simple u passing invalid arguments that’s why you got different result in different query let give short answer both queries are different that’s why u got different result

Yeah, I guess I can see how one could infer that I implied the codename for SQL Server 2014 was Hekaton. But it’s precisely those Hekaton features that Dave Ballantyne and I were writing about. I guess to be clearer, I should have written “SQL Server 2014’s Hekaton”