i am not sure what is going wrong here. i might be missing some basics, going bonkers. need some desperate help to find out the reason for strange behavior. It could be service pack issue, but incase its not...
sql server 2000 sp3
database is on d drive on 2003 server with more than 10GB free space
database has 1 table t1, around 18 column/fields all varchar with 1 bigint field which is primary key, more than 28 million records
7 indexes on 7 different fields - one each
[in context to example below] field3 varchar(10) - (not primary key, not foreign key, no default) - nonclustered index with fill factor = 90
connection timeout is set to 100 - using DAO
a) query used in the source - throws timeout / execution cancelled error - select field1, field2 from t1 where field3 like 'ABC 0HZ%'
b) query used in the source - works fine (without Z) - select field1, field2 from t1 where field3 like '0H%'
c) query used in the source - works fine (without %) - select field1, field2 from t1 where field3 like 'ABC XYZ 0HZ' or - select field1, field2 from t1 where field3 = 'ABC XYZ XYZ 0HZ'
query a above throws timeout error while using like operator to do a wild search for input data ending with Z
queries b and c works fine
- Sql server restarted - rebuild index - dropped and recreated - database detached and attached again - database shrinked after dropping indexes
Hope the point clear - Any help would be highly appreciated
Thanking you in advance