>>This could happen on very large tables. SQL server has a costbase optimizer. It means that it does do some statistical analysis on the usable indexes and the distribution of values.
>>
>>In the first query it could decide to use the index on Field3 and not to use the index on field2. It saves the executionplan and does use this for the second query. Since the index on field 3 then returns a lot of records now and it essentially does a table scan on field2, you'll get a dreadfull performance.
>>
>>I've identified a lot of performance problems on SQL server being caused by cached executionplans. Flushing them almost always solves them... until it occurs again.
>>
>>Not using parameters will prohibit the two queries to use the same executionplan and therefore will not have this problem.
>>
>>I understand that SQL2005 has a WITH RECOMPILE hint that will resolve this issue, but you're basically in dead water with SQL2000.
>
>Well upgrade then. What can happen w//o parameters is much more serious.
>Cetin

Well basically I don't have the authority to upgrade the SQL servers...Those are the clients responsibility... BTW, if you want to create serverside cursors (SELECT * INTO #CURSOR FROM ...), you cannot even use parameters. I know the risk of SQL injection is serious and we use paramerters where applicable, but there are circumstances where we simply cannot use them and are doing what we can to prevent SQL injection.