OPTION KEEPFIXED PLAN doesn''t work in IF statement?

Question

OPTION KEEPFIXED PLAN doesn''t work in IF statement?

mannadba

SSC Eights!

Points: 989
More actions
August 2, 2006 at 7:41 pm

#172043

Can anyone confirm that OPTION (KEEP PLAN) and OPTION (KEEPFIXED PLAN) shouldn't work as part of an IF statement? We reference a temp table in an IF statement (IF EXISTS (SELECT * FROM #temp)) and we would like to use the KEEPFIXED PLAN option to prevent recompilations. It seems to work fine elsewhere in the procedure but gives a syntax error when in the IF statement. Setting a variable with a count(*) works, but that's a lot of code to change.
Any ideas?
Sample code:
This works:
DECLARE @blahint int
CREATE TABLE #blah (blahfield int)
SELECT @blahint = count(*)
FROM #blah
OPTION (KEEPFIXED PLAN)
IF @blahint = 0
BEGIN
SELECT 'table empty'
END
DROP TABLE #blah
This works:
CREATE TABLE #blah (blahfield int)
IF (
(SELECT count(*) FROM #blah) = 0
)
BEGIN
SELECT 'table empty'
END
DROP TABLE #blah
This doesn't work:
CREATE TABLE #blah (blahfield int)
IF (
(SELECT count(*) FROM #blah OPTION (KEEPFIXED PLAN)) = 0
)
BEGIN
SELECT 'table empty'
END
DROP TABLE #blah

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I wasn't aware the keep plan option resolved recompiles with temp tables. If the dataset is small I suggest you use a table variable.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

mannadba SSC Eights! Points: 989 More actions · Answer 2

The temp table is actually created outside the procedure, so it can't be a temp table variable. It would have to be a real table with some additional programming.

That said, when you reference a temp table created outside the proc, SQL Server recompiles the proc-- or at least that statement-- at runtime no matter what (since it can't make heads or tails of it at compile time). So this won't really help my situation much, but it ought to work in the IF statement darnit.

That said, yes, KEEP PLAN will remove the "alter 7 rows of a temp table and I recompile" logic and KEEPFIX PLAN will say "don't recompile this query for optimality reasons (only correctness reasons".