Heh... there are no shades of gray. Either the code is fast or slow. Whether you choose to work on it or not is a decision. 🙂

Anyway, I've seen it on my home machine where the literal is always faster than the variable. That's using 2K5 Developer's Edition (virtually identical to the Enterprise Edition) on a 32 bit box. I'm not there right now or I'd show you the runs from that.

On the 2K5 Enterprise Edition 64K Box at work, the two pieces of code take turns winning within milliseconds of each other on a million row table. Not that I used dump-variables to take the display out of the picture because I don't want to measure time to display. The execution plan on both is identical to the other including % of batch.

As a bit of a sidebar, using % of batch to figure out which piece of code is more efficient is a very bad thing to do. Even after an actual run, it's still an estimate and I've seen it be 100% incorrect. I even gave Grant Fritchey the example and he included it in his APress book on execution plans.

Trying to test such problems with only 2 rows won't get anyone anywhere. It's easy to make a million row test table for such things in seconds. Here's some code that does just such a thing for this problem.

-- DROP TABLE tempdb.dbo.Table7

SELECT TOP 1000000

PK_Value = IDENTITY(INT,1,1)

, Field_Value = CASE WHEN ABS(CHECKSUM(NEWID()))%2 = 0 THEN 'No' ELSE 'Yes' END

INTO tempdb.dbo.Table7

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

ALTER TABLE tempdb.dbo.Table7

ADD PRIMARY KEY CLUSTERED (PK_Value)

;

CREATE INDEX IX_Table7_Field_Value

ON tempdb.dbo.Table7 (Field_Value)

;

Here's the code I used to test with. Like I said, I used variables to take the display time out of the picture. It's also worth noting that you should never use SET STATISTICS when testing User Defined Fuctions because it adds a huge amount of time to Scalar Functions in particular.

DECLARE @PK_Value INT

, @Field_Value CHAR(3)

;

SET STATISTICS TIME ON

;

DECLARE @yes VARCHAR(3)

SELECT @yes = 'Yes'

;

SELECT @PK_Value = PK_Value

, @Field_Value = Field_Value

FROM tempdb.dbo.Table7

WHERE Field_Value = @yes

;

SELECT @PK_Value = PK_Value

, @Field_Value = Field_Value

FROM tempdb.dbo.Table7

WHERE Field_Value = 'Yes'

;

GO

SET STATISTICS TIME OFF

;