Data Type mismatch may cause index failure

Sybase and SQL Server differ in this regard. If the data types in the condition comparison do not match, index failure may occur, resulting in potential performance problems.
Simple Description:

Create   Table Test (
C1 Int   Not   Null ,
C2 Money   Default   0 ,
C3 Varchar ( 20 ),
Constraint Pk_test Primary   Key (C1 ))
Go

Create   Index Ind_c2_test On Test (C2)
Go

After inserting some data, we can test the following:
1> set showplan on
2> go
1> declare @ var_int int
2> select @ var_int = 2
3> select * from test where c1 = @ var_int
4> go

Query plan for Statement 1 (at line 1 ).

Step 1
The type of query is declare.

Query plan for Statement 2 (at line 2 ).

Step 1
The type of query is select.

Query plan for Statement 3 (at line 3 ).

Step 1
The type of query is select.

from Table
test
nested iteration.
using clustered index.
index: pk_test
Forward scan.
positioning by key.
keys are:
C1 ASC
using I/O size 2 Kbytes for data pages.
with LRU buffer replacement strategy for data pages.

(1 row affected)
C1 C2 C3
-------------------------------------------------------
2 129.14 hellen

(1 row affected)
We can see that Sybase's execution plan uses clustered index to read data.

The following uses the money type for testing.
1> declare @ var_money money
2> select @ var_money = 2
3> select * from test where c1 = @ var_money
4> go

Query plan for Statement 1 (at line 1 ).

Step 1
The type of query is declare.

Query plan for Statement 2 (at line 2 ).

Step 1
The type of query is select.

Query plan for Statement 3 (at line 3 ).

Step 1
The type of query is select.

From table
Test
Nested iteration.
Table scan.
Forward scan.
Positioning at start of table.
Using I/O size 2 Kbytes for data pages.
With LRU buffer replacement strategy for data pages.

(1 row affected)
C1 C2 C3
-------------------------------------------------------
2 129.14 hellen

(1 row affected)

We can see that Sybase uses full table scanning instead of indexing.

In fact, Sybase does not necessarily use indexes because of different types. Instead, it has a matching principle. In principle, an index is used as long as the type priority of the index column is higher than the data type of the search condition.
This priority can be queried through the master. DBO. policypes.
1> select hierarchy, name from Master. DBO. policypes
2> order by 1
3> go
Hierarchy name
---------------------------------------
1 floatn
2 float
3 datetimn
4 datetime
5 real
6 numericn
7 numeric
8 decima1n
9 decimal
10 moneyn
11 money
12 smallmoney
13 smalldatetime
14 INTN
15 int
16 smallint
17 tinyint
18 bit
19 univarchar
20 unichar
22 sysname
22 varchar
22 nvarchar
23 char
23 nchar
24 Timestamp
24 varbinary
25 binary
26 text
27 Image
99 extended type