Loundy (2/17/2012)

---

Hi All,

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));

GO

CREATE INDEX IX_Col ON TestTable (Col);

GO

Then created the procedure:

CREATE PROCEDURE Test_Procedure

@Get NVARCHAR(20)

AS

SELECT ID, Col

FROM TestTable

WHERE Col = @Get;

Then populated the table with some random data:

DECLARE @int INT;

SET @int = 1;

WHILE @int <100

BEGIN

INSERT INTO TestTable (id, col)

VALUES (@int, 'name' + CAST(@int AS VARCHAR(100)));

SET @int += 1;

END

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....

Cheers, 🙂