Since the optimizer computes its estimates before a query executes, it cannot know the value of a variable that is set in the batch or procedure. If the value of a variable is not known at compile time, the optimizer uses the default values shown in Table 19-2
For example, the value of @city is set in this batch:
declare @city varchar(25)
select @city = city from publishers
where pub_name = "Brave Books"
select au_lname from authors where city = @city
The optimizer uses the total density, .000879, and estimates that 4 rows will be returned; the actual number of rows could be far larger.
A similar problem exists when you set the values of variables inside a stored procedure. In this case, you can improve performance by splitting the procedure: set the variable in the first procedure and then call the second procedure, passing the variables as parameters. The second procedure can then be optimized correctly.
See “Splitting stored procedures to improve costing” for an example.
