The effect of indexes on the order of query conditions

Index | conditions
Original post address:
http://community.csdn.net/expert/FAQ/FAQ_Manage.asp?id=0&Topicid=3390663

Environment: SQL Server2000 +SP4

Problem:
Select DateDiff (Day, ' 20040910 ', ' 20040920 ')--this sentence can be executed

--and the following sentence cannot be executed (sometimes it can be executed)
--sub_para is varchar (8) and the error message is: A syntax error occurred while converting from string to DateTime.
SELECT * FROM T_sub
where item_local_code= ' 03004 '
and DateDiff (Day,sub_para,getdate ()) =29
and (sub_del_flag<>1)

--and this statement does not return any recordset when it cannot be executed.
SELECT * FROM T_sub
where item_local_code= ' 03004 '
and IsDate (Sub_para) =0

-------------------------------------------------------------------------
--reason, the index created in the table affects the order in which the conditions are executed
--led to the execution of DateDiff (Day,sub_para,getdate ())

--The following test illustrates the problem
--Test table and data
CREATE TABLE TB (
Item_local_code char (5),
Sub_del_flag int,
Sub_para varchar (10),
Constraint pk_t primary KEY (Sub_para,item_local_code)
)
Insert TB Select ' 03004 ', 1, ' 2003-1-1 '
UNION ALL SELECT ' 03005 ', 1, ' 2003a1-1 '
Go

--Query statement
SELECT * FROM (
SELECT * FROM TB
where item_local_code= ' 03004 '
and sub_del_flag<>0
and IsDate (Sub_para) =1
A where DateDiff (Day,sub_para,getdate ()) >29
Go

--Delete test
DROP table TB

/*--test Results

Item_local_code Sub_del_flag Sub_para
--------------- ------------ ----------
03004 1 2003-1-1

Server: Message 241, Level 16, State 1, line 3
A syntax error occurred while converting from string to DateTime.

--*/