Subqueries in BETWEEN and CASE Statements

re: Subqueries in BETWEEN and CASE Statements

Craig Freedman — Wed, 02 Jan 2013 23:58:33 GMT

The BETWEEN clause and CASE statements are expanded fairly early in the processing of these queries so the workarounds should be fairly safe. However, I'm reluctant to make any absolute guarantee as plans can and do change based on a variety of factors.

HTH

Craig

re: Subqueries in BETWEEN and CASE Statements

Martin Smith — Mon, 31 Dec 2012 00:38:44 GMT

Can any of these work arounds be regarded as guaranteed behaviour or is using a variable the only sure fire way to prevent re-evaluation as per the earlier Connect Item?

re: Subqueries in BETWEEN and CASE Statements

Greig — Mon, 08 Nov 2010 22:24:39 GMT

Very usefull blog, thank you

re: Subqueries in BETWEEN and CASE Statements

dbenoit — Tue, 08 Jul 2008 18:48:54 GMT

Great blog with excellent information. I agree that this is "hard to believe" behavior but it is great to know about it so that we can address things appropriately. Now if only I could find a way to programatically find where in code these things would be occurring. : )

I will certainly put my vote in on the Connect site.

re: Subqueries in BETWEEN and CASE Statements

Daniel Smith — Thu, 03 Jul 2008 01:04:42 GMT

Thanks for the feedback Craig :-)

I've voted for the bug on Connect, so hopefully the query optimizer can be improved in the next version.

It just seems a bit crazy that the optimizer in 2000 could handle this type of thing, yet 2005 and 2008 can't. It's even more frustrating to hear that we might have to wait for the next version after 2008, which is likely at least 3 years away!

re: Subqueries in BETWEEN and CASE Statements

Craig Freedman — Wed, 02 Jul 2008 21:39:46 GMT

I forwarded your feedback to the appropriate individuals. Nevertheless, I would still strongly recommend that you submit your feedback directly at http://connect.microsoft.com/. See also http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=336002.

Craig

re: Subqueries in BETWEEN and CASE Statements

Daniel Smith — Sat, 28 Jun 2008 18:04:53 GMT

So what are the plans to address this? This is totally unexpected behaviour.

Will this be fixed in the next service pack for 2005? In the meantime, I hope a hotfix is being worked on as a high priority.

re: Subqueries in BETWEEN and CASE Statements

chojrak11 — Sat, 28 Jun 2008 10:24:56 GMT

Thanks for the article. It made me spew out amazingly rich stream of curses. I've been fighting SQL 2005 performance problems for a year or so, many of which are exactly with CASE and BETWEEN, and today you tell me it's designed to act this way...

Thanks for clear explanation why things slowed down (for me at last), instead of being 30% faster, as advertised.

I hope that this weird behaviour, for which I don't see any justification, is going to be eliminated in SQL 2008, and in next SP for SQL 2005.

Have a nice day.