Database Reference
In-Depth Information
where 1=1'
if @Number is not null
select @Sql = @SQL + N' and Number=@Number'
if @Name is not null
select @Sql = @SQL + N' and Name=@Name'
exec sp_executesql @Sql, N'@Number varchar(32), @Name varchar(100)'
,@Number=@Number, @Name=@Name
end
■
Note
We demonstrated a similar problem of breaking ownership chaining with CLr code in Chapter 13, “CLr.”
■
Important
always use parameters with the
sp_executesql
procedure to avoid SQL injection.
Remember this behavior when you are using filtered indexes. SQL Server will not generate and cache a plan that
uses a filtered index, in cases when that index cannot be used with some combination of parameter values. Listing 26-15
shows an example. SQL Server will not generate the plan, which is using the
IDX_Data_UnprocessedData
index, even
when the
@Processed
parameter is set to zero because this plan would not be valid for a non-zero
@Processed
parameter value.
Listing 26-15.
Plan Reuse: Filtered Indexes
create unique nonclustered index IDX_Data_UnprocessedData
on dbo.RawData(ID)
include(Processed)
where Processed = 0;
-- Compiled Plan for the query would not use filtered index
select top 100 *
from dbo.RawData
where ID > @ID and Processed = @Processed
order by ID;
Plan Caching for Ad-Hoc Queries
SQL Server caches plans for ad-hoc queries (and batches), which use constants rather than parameters in the
where
clause. Listing 26-16 shows an example of ad-hoc queries.
Listing 26-16.
Ad-hoc queries
select * from dbo.Customers where LastName='Smith'
go
select * from dbo.Customers where LastName='Smith'
go
SELECT * FROM dbo.Customers WHERE LastName='Smith'
go
select * from dbo.Customers where LastName = 'Smith'
go
