Using IF in T-SQL weakens or breaks execution plan caching?

Question

It has been suggest to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation of this assertion. I'm using SQL Server 2005 and 2008.

The assertion is that with the following batch:-

IF @parameter = 0
 BEGIN
  SELECT ... something
 END

ELSE
 BEGIN
  SELECT ... something else
 END

SQL Server cannot re-use the execution plan generated because the next execution may need a different branch. This implies that SQL Server will eliminate one branch entirely from execution plan on the basis that for the current execution it can already determine which branch is needed. Is this really true?

In addition what happens in this case:-

IF EXISTS (SELECT ....)
 BEGIN
  SELECT ... something
 END

ELSE
 BEGIN
  SELECT ... something else
 END

where it's not possible to determine in advance which branch will be executed?

Answer 1

Try to display the estimated execution plan, not the actual. You'll see that first one contains COND operator.

Answer 2

Plans will be created based on the parameters passed so in reality I'd say No - having conditional logic which is normally based on parameters is not detrimental to performance.

You will get multiple plans produced, assuming the parameters cause enough of a variance for the query optimizer to notice.

You can see which by switching on the Show Execution plan, running the scripts - notice the differences in the plan. When you run the procedures (I am assuming stored procedures here), you will notice the first-time is generally quicker, the second hit uses the stored plan. Change parameters and repeat then run the original parameters - in theory the plan will still be in cache but it does depend on server usage (cache ticks - they dont stay forever..) etc.

Answer 3

The only shortcut will be IF 1 = 1

Both @parameter and EXISTS still requires processing for the "general case" (@parameter = 42 say)

Saying that... what does the actual execution plan say as well as profiler capturing recomplition events? (I dislike estimated plans as per Jao's answer)