Simple paramaterization occurs when the optimizer determines that a query would benefit from a reusable plan, so it takes the hard coded values and converts them to a parameter. Great stuff. But…
Let’s take this example. Here’s a very simple query:
SELECT ct.* FROM Person.ContactType AS ct WHERE ct.ContactTypeID = 7;
This query results in simple parameterization and we can see it in the SELECT operator of the execution plan:
We can also see the parameter that was defined in use in the predicate of the seek operation:
Who the heck put the wrong data type in there that’s causing an implicit conversion? The query optimizer did it. Yeah. Fun stuff. If I change the predicate value to 7000 or 700000 I’ll get two more plans and I can see them all by querying the cache. But, here’s a fun little bit. I just searched the XML of the plan, the data type isn’t stored anywhere that I can see. But, if you query the plan cache, look what the text of the query is:
(@1 tinyint)SELECT [ct].* FROM [Person].[ContactType] [ct] WHERE [ct].[ContactTypeID]=@1
Now, in this case, this type of conversion doesn’t hurt performance in any way. We’re still getting an index seek. I also ran some tests with strings. The optimizer was setting them to VARCHAR(8000) and I got an implicit conversion that, again, didn’t hurt performance. But, I wouldn’t be completely shocked, if, at some point, that lead to scans. What I find it really interesting is, that the data type isn’t stored with the plan. I fully expected it to be there.