Database Reference
In-Depth Information
create unique index IDX_Customers_Name
on dbo.Customers(CustomerName)
go
create proc dbo.SearchCustomerByName
(
@CustomerName varchar(64)
)
as
select CustomerId, CustomerName, PlaceHolder
from dbo.Customers
where CustomerName = @CustomerName
go
exec sp_executesql
@SQL =
N'select CustomerId, CustomerName, PlaceHolder
from dbo.Customers
where CustomerName = @CustomerName'
,@Params = N'@CustomerName nvarchar(64)'
,@CustomerName = N'Customer';
exec sp_executesql
@SQL = N'exec dbo.SearchCustomerByName @CustomerName'
,@Params = N'@CustomerName nvarchar(64)'
,@CustomerName = N'Customer';
As you can see in Figure
16-2
, dynamic SQL generates the plan with a
Clustered Index Scan,
implicitly converting
the
CustomerName
value from every row to
nvarchar
. A stored procedure, on the other hand, performs that conversion
at the initial assignment of parameter values and generates a much more efficient execution plan with the
Index Seek
operation.
