Tuesday, December 27, 2005

OPTION FAST vs CONVERT

I was playing around with the OPTION FAST statement for an update query when I saw a 'weird' behavior.

When using the OPTION FAST SQL Server passed the criteria as a fixed value while without it, it would use a variable because of a conversion.

Then I remembered the following article which states a list of situations where auto-parameterization is not happening and one of it is:

A statement with query hints specified using the OPTION clause.

I was passing a uniqueidentifier between single quotes so apparently the auto-parameterization is storing it as a character variable in the cache.

Another mystery solved :-)

No comments: