-- Create a table with two columns with equal content but different data types
CREATE TABLE	dbo.ImplicitConversion
		(
			RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			String VARCHAR(11) NOT NULL,
			Number INT NOT NULL
		)

-- Populate the table with a preset value
INSERT	dbo.ImplicitConversion
	(
		String,
		Number
	)
VALUES	('123', 123)

-- Populate the table with 6M+ random values
INSERT	dbo.ImplicitConversion
	(
		String,
		Number
	)
SELECT	Random AS String,
	Random AS Number
FROM	(
		SELECT		CHECKSUM(NEWID()) AS Random
		FROM		master.dbo.spt_values AS v
		CROSS JOIN	master.dbo.spt_values AS w
	) AS d

-- Create proper indexes
CREATE NONCLUSTERED INDEX IX_String ON dbo.ImplicitConversion (String)
CREATE NONCLUSTERED INDEX IX_Number ON dbo.ImplicitConversion (Number)

-- Get row with proper data type (SEEK)
SELECT	Number
FROM	dbo.ImplicitConversion
WHERE	Number = 123

-- Get row with improper data type (SEEK)
SELECT	Number
FROM	dbo.ImplicitConversion
WHERE	Number = '123'

-- Get row with proper data type (SEEK)
SELECT	String
FROM	dbo.ImplicitConversion
WHERE	String = '123'

-- Get row with improper data type (SCAN)
SELECT	String
FROM	dbo.ImplicitConversion
WHERE	String = 123

-- Clean up
DROP TABLE dbo.ImplicitConversion

quote:

---

Originally posted by SwePeso

The implicit conversion occurs at the fourth select statements, due to datatype presedence.
The WHERE clause decides that the column is a string and the argument is a number, and number has higher presedence so all rows for this column gets converted into number too.

Look at the execution plans (last 4) and check the PREDICATE. For two of them you get IMPLICIT_CONVERSION but only one of these two turns into a scan instead of the expected SEEK.

---

N 56°04'39.26"
E 12°55'05.63"

---

INNER JOIN (
		SELECT
			rsc.cuid
			, s.impression_date
			, SUM(s.impressions) AS tot_io_imp_dt
		FROM
			iv_performance_test AS s with (nolock)
			INNER JOIN dbo.ref_sub_campaign AS rsc ON s.sc_id = rsc.sc_id AND s.platform = rsc.dsp_name
		GROUP BY
			rsc.cuid
			, s.impression_date
			)
			AS io_imp_dt ON
				io_imp_dt.cuid = io.cuid 
				AND io_imp_dt.impression_date = a.impression_date 
				
	INNER JOIN (
		SELECT
			rsc.cuid
			, SUM(s.impressions) AS tot_io_imp
			, SUM(s.clicks) AS tot_io_clicks
			, SUM(s.contract_actions) AS tot_io_actions
		FROM
			iv_performance_test AS s with (nolock)
			INNER JOIN dbo.ref_sub_campaign AS rsc ON s.sc_id = rsc.sc_id AND s.platform = rsc.dsp_name
		GROUP BY
			rsc.cuid
			)
			AS io_imp ON io_imp.cuid = io.cuid

tot_io_imp
tot_io_clicks
tot_io_actions