Hi SQL Padawan,

I coded up your example to explain the difference between estimated execution plans and actual I/O statistics.

Both of your statements use the idx_field_value in the query plan.

In my example below, I could not get the query analyzer to use the this index due to the amount of data in the table (2 records).

It performs a full table scan (FTS).

Here are some things to note.

1 - The plan with a constant will probably be re-used. This is due to the fact that we know what the value is.

On the other hand, this may lead to parameter sniffing.

2 - The plan with the variable will be recompiled every time. This is due to the fact it might change.

This is even more likely if the input parameter is set to a local variable before executing. And the local variable is used in the where clause.

Please see Grant Fritchey query plan e-book from simple talk.

I usually look at time and I/O when running a query. The below TSQL with turn these counters on in the query window.

-- Show time & i/o

SET STATISTICS TIME ON

SET STATISTICS IO ON

GO

I usually clear the buffers and plan cache in test so that stored plans do not skew the results.

-- Remove clean buffers & clear plan cache

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

My simple execution using two records show that the non-parameter solution is 1 ms quicker than the one with a variable.

Both have the same page scans (logical and physical).

Please redo clearing the buffers and cache and post the results for I/O.

If they are almost the same, there is really not difference between the two constructs other than possibly removing parameter sniffing.

Sincerely

John

PS: I hope this post helps you out!

:w00t:

--

-- Sample code

--

-- Create temp table

create table tempdb.dbo.table7

(

[pk_value] int,

[field_value] varchar(10)

);

-- Create NC index

create nonclustered index idx_field_value on tempdb.dbo.table7 ([field_value]);

-- Insert two rows

insert into tempdb.dbo.table7 values

(1, 'Yes'),

(2, 'No');

-- Update the stats

update statistics tempdb.dbo.table7;

go

-- Show the data

select * from tempdb.dbo.table7

-- Show time & i/o

SET STATISTICS TIME ON

SET STATISTICS IO ON

GO

-- Remove clean buffers & clear plan cache

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

-- Declare local variable

declare @yes varchar(3) = 'Yes'

-- First way

select

[pk_value]

,[field_value]

from tempdb.dbo.table7

where [field_value] = @yes;

/*

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 18 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

Table 'table7'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 14 ms.

*/

-- Remove clean buffers & clear plan cache

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

-- Second way

select

[pk_value]

,[field_value]

FROM tempdb.dbo.table7

WHERE [field_value] = 'Yes';

/*

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 22 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

Table 'table7'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 13 ms.

*/