Sybase and SQL Server differ in this, if the data type in the condition is not matched, it may cause an index to fail, resulting in potential Performance issues.
Briefly illustrate as follows:
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 as follows: 1> set showplan on2> Go1> declare @var_int int2> select @ var_int = 23> SELECT * from test where c1 = @ var_int4> 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 Itex. 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 C3 ------------------------------------------- ------------- 2 129.14 Hellen
(1 Row Affected) We see that Sybase's execution plan uses Clustered Index to read data. Below, use the Money type to test 1> declare @var_money Money2> SELECT @ var_money = 23> SELECT * from test where c1 = @ var_money4> 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 Items. 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 C3 C3 ---- ------------------------------------------------- - 2 129.14 Hellen
(1 row affed) We can see that Sybase has no index, but a full table scan. In fact, Sybase is not a type inconsistency, it will not use indexes, but there is a matching principle, in principle, as long as the type of index column is higher than the data type of the search criteria, it will use the index. This priority can pass the query system table master.dbo.systypes.1> select hierarchy, name from master.dbo.systypes2> Order by 13> 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
