Avoid using variables in the WHERE clause of a query located in a batch file. Let's find out why this may not be a good idea.

First, let's look at the following code:

SELECT employee_id
FROM employees
WHERE age = 30 and service_years = 10

Assuming that both the age and the service_years columns have indexes, and the table has many thousands of records, then SQL Server's Query Optimizer will select the indexes to perform the query and return results very quickly.

Now, let's look at the same query, but written to be more generic, one that you might find in a generic batch file:

DECLARE @age int
SET @age = "30"
DECLARE @service_years int
SET @service_years = "10"
SELECT employee_id
FROM employees
WHERE age = @age and service_years = @service_years

When the above code is run, even though both the age and the service_years columns have indexes, they may not be used, and a table scan may be used instead, potentially greatly increasing the amount of time for the query to run.

The reason the indexes may not be used is because the Query Analyzer does not know the value of the variables when it selects an access method to perform the query. Because this is a batch file, only one pass is made of the Transact-SQL code, preventing the Query Optimizer from knowing what it needs to know in order to select an access method that uses the indexes.

If you cannot avoid using variables in the WHERE clauses of batch scripts, consider using an INDEX query hint to tell the Query Optimizer to use the available indexes instead of ignoring them and performing a table scan. This of course that the indexes are highly selective. If the indexes are not highly selective, then a table scan most likely be more efficient than using the available indexes.

Another option is to not use a script, but a stored procedure instead. Variables in stored procedures don't cause the problem described above.