• 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

    */