Brandie Tarvin (3/27/2015)
ChrisM@Work (3/27/2015)
TomThomson (3/27/2015)
dwain.c (3/26/2015)
...Edit: This query shows the same thing:
IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
Which means that it has something to do with the 100% cost calculated for the Constant Scan generated for the IF. The remainder of the query must be getting its costs rated against that.
Add this to the list of SQL Developer interviewee questions...
There are some amusing variants - for example try changing the condition:
😀
Nice one, Tom - it's the simplest yet and on this server I see 27 million % cost for one of the operators :w00t:
Color me confused. Tom's code looks the same as Dwain's.
???
Color me butter-fingered! I meant
IF EXISTS (SELECT 2 FROM (VALUES(CAST(1 AS BIT))) d (n))
SELECT * FROM sys.all_columns;
Numbers not quite so big! But look at the actual execution plan (not the estimated one, which doesn't differ from the SELECT 1 case). 95% of the work is in the branch which isn't needed. It's a nice illustration of why (apart from generating spurious run-time errors which can be a nightmare to get rid of) an eager mode of evaluation is unacceptable unless the optimiser takes the trouble to avoid the obvious problems. (I expect Paul will jump on me for that comment - maybe I shouldn't be technical in this thread :hehe:)
Tom