数据类型的不匹配可能会导致索引失效

        Sybase和SQL Server在这一点上有所不同，如果条件比较中的数据类型不匹配的话，可能会引起索引失效，导致潜在的Performance问题。
       简单说明如下：
       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

插入一些数据后，我们可以测试如下：
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)
我们看到，sybase的执行计划会使用clustered index来读取数据。

下面，采用money类型来进行测试
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)

我们可以看到，sybase没有采用索引，而是采用了全表扫描。

实际上，Sybase并不是类型不一致就一定不会使用索引，而是有一个匹配原则，原则上是只要索引列的类型优先级高于搜索条件的数据类型，就会使用索引。
这个优先级，可以通过查询系统表master.dbo.systypes.
1> select hierarchy,name from master.dbo.systypes
2> order by 1
3> go
 hierarchy name
 --------- ------------------------------
         1 floatn
         2 float
         3 datetimn
         4 datetime
         5 real
         6 numericn
         7 numeric
         8 decimaln
         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

本文地址：http://com.8s8s.com/it/it28285.htm