SQL Server Stored Procedure - 'IF statement' vs 'Where criteria'

query-optimization, sql-server, t-sql

Solution

SELECT Departments.* FROM Departments, Employees
    WHERE Departments.DepartmentId = Employees.DepartmentId 
    AND Employees.EmployeeId = @EmployeeId 
    AND @IncludeDepartmentInfo = 1

When SQL compiles a query like this it must be compiled for any value of @IncludeDepartmentInfo. The resulted plan can well be one that scans the tables and performs the join and after that checks the variable, resulting in unnecessary I/O. The optimizer may be smart and move the check for the variable ahead of the actual I/O operations in the execution plan, but this is never guaranteed. This is why I always recommend to use explicit IFs in the T-SQL for queries that need to perform very differently based on a variable value (the typical example being OR conditions).

gbn's observation is also an important one: from an API design point of view is better to have a consistent return type (ie. always return the same shaped and number of result sets).

Problem

The question from quite a long time boiling in my head, that out of the following two stored procedures which one would perform better. Proc 1 ``` CREATE PROCEDURE GetEmployeeDetails @EmployeeId uniqueidentifier, @IncludeDepartmentInfo bit AS BEGIN SELECT * FROM Employees WHERE Employees.EmployeeId = @EmployeeId IF (@IncludeDepartmentInfo = 1) BEGIN SELECT Departments.* FROM Departments, Employees WHERE Departments.DepartmentId = Employees.DepartmentId AND Employees.EmployeeId = @EmployeeId END END ``` Proc 2 ``` CREATE PROCEDURE GetEmployeeDetails @EmployeeId uniqueidentifier, @IncludeDepartmentInfo bit AS BEGIN SELECT * FROM Employees WHERE Employees.EmployeeId = @EmployeeId SELECT Departments.* FROM Departments, Employees WHERE Departments.DepartmentId = Employees.DepartmentId AND Employees.EmployeeId = @EmployeeId AND @IncludeDepartmentInfo = 1 END ``` the only difference between the two is use of 'if statment'. if proc 1/proc 2 are called with alternating values of @IncludeDepartmentInfo then from my understanding proc 2 would perform better, because it will retain the same query plan irrespective of the value of @IncludeDepartmentInfo, whereas proc1 will change query plan in each call answers are really appericated PS: this is just a scenario, please don't go to the explicit query results but the essence of example. I am really particular about the query optimizer result (in both cases of 'if and where' and their difference), there are many aspects which I know could affect the performance which I want to avoid in this question.

Original source