Datatypes for parameters and variables used as SARGs

When declaring datatypes for variables or stored procedure parameters to be used as search arguments, match the datatype of the column in the variable or parameter declaration to ensure the use of an index. For example:

declare @int_var int 
select @int_var = 50 
select * 
from t1 
where int_col = @int_var

Use of the index depends on the precedence of datatypes in the hierarchy. The index on a column can be used only if the column’s datatype precedes the variable’s datatype. For example, int precedes smallint and tinyint in the hierarchy. Here are just the integer types:

hierarchy  name
 --------- ------------------------------
        15 int
        16 smallint
        17 tinyint

If a variable or parameter has a datatype of smallint or tinyint, an index on an int column can be used for a query. But an index on a tinyint column cannot be used for an int parameter.

Similarly, money precedes int. If a variable or parameter of money is compared to an int column, an index on the int column cannot be used.

This eliminates issues that could arise from truncation or overflow. For example, it would not be useful or correct to attempt to truncate the money value to 5 in order to use an index on int_col for this query:

declare @money_var money 
select @money_var = $5.12 
select * from t1 where int_col = @money_var