Thanks all for the replies.
To clarify a couple of points. The variable is being used as a constant in the code. @yes will always equal 'Yes'. Rather than have a type-o, the developer will declare @yes at the top of the code and use it rather than typing 'Yes' multiple times in the code. Apply this logic to a more complex example, and hopefully you get the idea.
Maybe I'm misunderstanding param sniffing, but isn't the optimizer going to generate a plan and keep it until something changes (index rebuilt\reorg, stats update, table records added\deleted, etc...), and then generate a new plan based on the updated objects? Where the variable value never changes, param sniffing won't come into play here?
The query in question can run multiple times a second, I was simply looking at the performance of using @yes vs 'Yes', a quick and dirty adhoc looking at the cost per batch looks like the variable option is more expensive.
Here's the entire script to recreate the results:
/*
USE tempdb
GO
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[table7]')
AND type IN (N'U')
)
DROP TABLE [dbo].[table7]
GO
CREATE TABLE dbo.table7 (
pk_value INT IDENTITY(1, 1) PRIMARY KEY
,field_value VARCHAR(255)
);
GO
INSERT INTO table7 (field_value)
VALUES ('Yes');
GO 5000
INSERT INTO table7 (field_value)
VALUES ('No');
GO 15000
IF EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[dbo].[table7]')
AND NAME = N'IX_field_value'
)
DROP INDEX [IX_field_value] ON [dbo].[table7]
WITH (ONLINE = OFF)
GO
CREATE NONCLUSTERED INDEX [IX_field_value] ON [dbo].[table7] ([field_value] ASC)
WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = OFF
,IGNORE_DUP_KEY = OFF
,DROP_EXISTING = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO
*/
/*
USE tempdb
GO
--Include Actual Execution Plan
DECLARE @yes VARCHAR(3) = 'Yes'
SELECT [pk_value]
,[field_value]
FROM [tempdb].[dbo].[table7]
WHERE [field_value] = @yes;
GO
SELECT [pk_value]
,[field_value]
FROM [tempdb].[dbo].[table7]
WHERE [field_value] = 'Yes';
GO
*/