It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate 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?
Best Answer
SQL Server optimises the process of compilation of the query plan for the stored procedure by ignoring the conditional branches inside the stored procedure. The plan will be generated based on the parameters used for the first execution, this will cause problems if the parameters are different for the branches.
I would place the SQL for each of the branches into their own stored procedure, so that the plan generated is based on the actual usage of parameters for that branch.