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
