Is there a performance difference between these, and if so, why?
SELECT Col1, Col2, Col3
FROM dbo.MYTable
WHERE Col17 = CONVERT (VARCHAR (10), @Parameter1)
and
SELECT Col1, Col2, Col3
FROM dbo.MYTable
WHERE CONVERT (VARCHAR (10), @Parameter1) = Col17
As long as you only use any function on the parameter, the query plan remains the same. If you alter your statement and use the conversion on the column you will get a different query plan, and SQL will use a table scan instead ( I assume you have an index on col17 ).
No, in this case with convert function not at the column table, the result and cost is the same
create table xxxx (col int)
go
create index aaa on xxxx(col)
go
insert into xxxx values (5)
go 10000
insert into xxxx values (3)
go 10000
insert into xxxx values (1)
go 10000
declare @Parameter1 int
set @Parameter1 = 5
SELECT * FROM xxxx WHERE Col = CONVERT (VARCHAR (10), @Parameter1)
**SELECT * FROM xxxx WHERE Col = CONVERT (VARCHAR (10), @Parameter1)
|--Index Seek(OBJECT:([DBA].[dbo].[xxxx].[aaa]), SEEK:([DBA].[dbo].[xxxx].
[col]=CONVERT_IMPLICIT(int,CONVERT(varchar(10),[@Parameter1],0),0)) ORDERED FORWARD)**
SELECT * FROM xxxx WHERE CONVERT (VARCHAR (10), @Parameter1) = Col
**SELECT * FROM xxxx WHERE CONVERT (VARCHAR (10), @Parameter1) = Col
|--Index Seek(OBJECT:([DBA].[dbo].[xxxx].[aaa]), SEEK:([DBA].[dbo].[xxxx].
[col]=CONVERT_IMPLICIT(int,CONVERT(varchar(10),[@Parameter1],0),0)) ORDERED FORWARD)**
Just to complement, you can optimize the statment with covered index, but in this case both in 1 case and in the second the index will be used in the same way and cost (includin IO statisics). And you can change the datatype in convert function, because the optimizer perfectly convert to correct dataype using CONVERT_IMPLICT. The correct is not that, its you use the same dataype and avoid convert_implicit. But if you really need use convert, in this case, with this 2 statments there is no difference because the convert function is not in the table column. If it were, will probably be a table scan or clustered index scan if the table had a clustered index
It depends on the datatype of Col17.
