Add Constants to TSQL for Better Query Plans - by jhess001

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 533825 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 2/15/2010 2:45:44 PM
Access Restriction Public
Primary Feedback Item 404252


When I declare a local variable in a stored procedure and assign it a value that never changes and then restrict a SELECT query based on that variable, the performance is worse than if I hard-code the constant in the SELECT's WHERE statement.  

Here's an example:

-- Create a table and populate it with some sparse data

create table SparseData (
	ID int not null identity(1, 1) ,
		constraint PK_SparseData_ID primary key ( ID ) ,
	IsProcessed bit not null default(0)

create index IX_SparseData_IsProcessed on SparseData( IsProcessed )
where IsProcessed = 1

declare @RowsToInsert int
set @RowsToInsert = 1000000
while (select count(*) from SparseData) < @RowsToInsert
	insert into SparseData(IsProcessed) values(0)

-- Looking at the execution plans for the following, the estimated 
-- rowcount differs

select * from SparseData  where IsProcessed = 1

declare @cTrue bit
set @cTrue = 1
select * from SparseData where IsProcessed = @cTrue

This causes worse query plans to be generated when using local variables than when using constants.  The problem is that using the variable makes the code more readable and harder to inject defects into than if I hard-code a constant throughout a proc.
Sign in to post a comment.
Posted by Microsoft on 3/3/2010 at 11:40 AM
I have resolved your request as duplicate of feedback item below:

Umachandar, SQL Programmability Team
Posted by Microsoft on 3/1/2010 at 9:02 AM

Thanks for your feedback. This is a well known issue and we're working to fix it by default at some point in the future. Consider for now as a workaround adding OPTION(RECOMPILE) to all the queries that use the variable. That will cause SQL Server to use the value during optimization ("sniff" it) rather than treating it as unknown and guessing the selectivity estimate. See for more details.