SET STATISTICS IO, TIME ON

IF OBJECT_ID('tempdb..#ImplicitConverts') IS NOT NULL
	DROP TABLE #ImplicitConverts

CREATE TABLE #ImplicitConverts 
(
	bigintNum BIGINT NOT NULL,
	tinyintNum TINYINT NOT NULL,
	varcharString VARCHAR(100) NOT NULL,
	nvarcharString NVARCHAR(100) NOT NULL
)

--Fast generate 10M records
--Took 13 mins to write the data out on my slow metal spinners
DECLARE @MaxNumber BIGINT = 9999999
;WITH Base AS
(
	SELECT 1 n
	UNION ALL
	SELECT n + 1
	FROM Base
	WHERE n < CEILING(SQRT(@MaxNumber))
),
ExpandBase AS
(
	SELECT 1 n
	FROM Base b1, Base b2
),
TallyTable AS
(
	SELECT ROW_NUMBER() OVER (ORDER BY n) n
	FROM ExpandBase
)
INSERT INTO #ImplicitConverts
SELECT n
	--Keep our tinyint within the bounds of the data type
	, (n / CEILING((CAST(n AS NUMERIC(10,2)) / CAST(250 AS NUMERIC(10,2)))))
	, CAST(n as varchar(10)) + 'varcharString'
	, CAST(n as varchar(10)) + 'NvarcharString'
FROM TallyTable
OPTION	( MAXRECURSION 0 );

CREATE NONCLUSTERED INDEX IX_bigint ON #ImplicitConverts (bigintNum)
CREATE NONCLUSTERED INDEX IX_tinyint ON #ImplicitConverts (tinyintNum)
CREATE NONCLUSTERED INDEX IX_varcharString ON #ImplicitConverts (varcharString)
CREATE NONCLUSTERED INDEX IX_nvarcharString ON #ImplicitConverts (nvarcharString)

/****************************************/
--TURN ON ACTUAL EXECUTION PLANS (ctrl-m)
/****************************************/

--Error due to incompatible conversion.

DECLARE @bigintParam BIGINT = 152

SELECT bigintNum, tinyintNum
FROM #ImplicitConverts
WHERE varcharString = @bigintParam

--Data type precedence
--https://msdn.microsoft.com/en-us/library/ms190309.aspx

--DO NOT USE IN PRODUCTION!!!
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

--Three index seeks because we happened to convert implicitly on the param side.

DECLARE @tinyintParam TINYINT = 152
DECLARE @varcharParam VARCHAR(100) = '152'

SELECT tinyintNum
FROM #ImplicitConverts
WHERE tinyintNum = @tinyintParam

SELECT bigintNum
FROM #ImplicitConverts
WHERE bigintNum = @tinyintParam

SELECT bigintNum
FROM #ImplicitConverts
WHERE bigintNum = @varcharParam

--DO NOT USE IN PRODUCTION!!!
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

--Index scan because we converted implicitly on the column side.

DECLARE @nvarcharParam NVARCHAR(100) = N'7371varcharString'

SELECT varcharString, nvarcharString
FROM #ImplicitConverts
WHERE varcharString = @nvarcharParam

--Explicit convert fix

SELECT varcharString, nvarcharString
FROM #ImplicitConverts
WHERE varcharString = CAST(@nvarcharParam AS VARCHAR(100))