Interesting question. I think i must be doing something wrong when testing this myself though.
I've created the table and index like so:
CREATE TABLE TestTable (ID INT, Col VARCHAR(100));
CREATE INDEX IX_Col ON TestTable (Col);
Then created the procedure:
CREATE PROCEDURE Test_Procedure
SELECT ID, Col
WHERE Col = @Get;
Then populated the table with some random data:
DECLARE @int INT;
SET @int = 1;
WHILE @int <100
INSERT INTO TestTable (id, col)
VALUES (@int, 'name' + CAST(@int AS VARCHAR(100)));
SET @int += 1;
Now, this does result in a table scan with the CONVERT_IMPLICIT operator.
If I then change the stored proc to use VARCHAR (or VARCHAR(100)) it still results in a table scan - however the CONVERT_IMPLICIT is gone.
Is this the correct behaviour? or am I doing something silly....
There's no kill switch on awesome!