Cast or Convert performance hit

Transact-SQL
1

Is there any implicit performance difference between these two? Notice the first time the params are all strings, but the fields are actually typed (INT, VARCHAR and BIT). The second time, the param is typed correctly. I am trying to quantify if there is anything gained from setting the types from my .NET code explicitly, or if using a generic method for the parameters will work just as well. Thoughts?

DECLARE @Num VARCHAR(MAX) = '1'
DECLARE @String VARCHAR(MAX) = 'THIS IS A TEST'
DECLARE @Bool VARCHAR(MAX) = 'true'

--STRONG TYPES

DECLARE @Num INT = 1
DECLARE @String VARCHAR(MAX) = 'THIS IS A TEST'
DECLARE @Bool BIT = 1



DECLARE @Table Table
(
    NumField INT PRIMARY KEY,
    StringField VARCHAR(100),
    BoolField BIT
)

INSERT INTO @Table
SELECT @Num, @String, @Bool

SELECT * FROM @Table
2

A related blog article that I wrote a few years ago: http://weblogs.sqlteam.com/tarad/archive/2007/11/16/60408.aspx

The links to the execution plan images are broken but there should be enough text in the article there to explain it. Or grab the code and test on your machine to see the execution plans.

3

varchar(max) will be slower than other data types. Numeric types are faster than string types. Those generalities stay true. Other generalities may depend on specific situations or settings.