How to get rid of implicit conversions?

Maahi 2016-08-02 05:51:14

Description : How to get rid IMPLICIT CONVERSION warnings in my JOIN conditions?

Env :
Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

CREATE TABLE dbo.T1 (
BigIntColumn BIGINT NOT NULL,
IntColumn INT NOT NULL —/// int datatype
);

CREATE TABLE dbo.T2 (
BigIntColumn BIGINT NOT NULL,
VarcharColumn varchar(10) NOT NULL, —///varchar datatype
);

— insert data into t1
— load 1000 row(s)
;
WITH Nums
AS (SELECT TOP (1000)
ROW_NUMBER() OVER (ORDER BY (SELECT 1
)) AS n
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
)
INSERT INTO dbo.t1
(BigIntColumn,
IntColumn
)
SELECT Nums.n,
Nums.n
FROM Nums;
go

— insert data into t2
— load 1000 row(s)

insert into t2
select * from t1

select top 10 * from t1
select top 20 * From t2

— type conversion warnings in Actual Execution plan. how to avoid it??

–try1: implicit warning

select
t1.IntColumn,
t2.VarcharColumn
from t1 inner join t2 on t1.IntColumn = t2.VarcharColumn
go

–try2: still seeing the warning. how to getrid of it ??
select
t1.IntColumn,
t2.VarcharColumn
from t1 inner join t2 on t1.IntColumn = cast(t2.VarcharColumn as int)
go

–try3: still seeing the warning. how to getrid of it??
select
t1.IntColumn,
t2.VarcharColumn
from t1 inner join t2 on t1.IntColumn = convert(int,t2.VarcharColumn)

Aaron Bertrand 2016-08-02 14:30:30
As I mentioned in your other question, why are you hyper-focused on these warnings? They indicate that the underlying data types don't match. Unless you can change the underlying data types or add computed columns and change the queries, you're not going to get a seek, so what do you expect to happen if you can somehow suppress the warnings?
Maahi 2016-08-02 15:02:35
Just hoping to somehow improve the performance of that query.
Aaron Bertrand 2016-08-02 16:00:00
Hiding the warning won't improve the performance. Making the data types match (or materializing the strings via a computed column or separate table) might, if there are suitable indexes to facilitate the join. There are two problems with your current design – one is that the data types for the join columns do not match, and conversion is necessary (whether it raises a warning or not, it is still an expensive conversion), and the other is that your join columns aren't indexed (which typically means a hash match or additional sort operators, neither of which are optimal).