>>>>David,
>>>>
>>>>See answers inline.
>>>>
>>>>>I'm using VFP9 to communicate with SQLServer database, using SQLEXEC(). I need to know the corresponding SQL commands for a few basic VFP commands:
>>>>>
>>>>>1) REPLACE table1.field1 WITH "HELLO" FOR "FRANCE" $ table1.field2
>>>>

>>>>UPDATE Table1 set field1 = 'HELLO' where Field2 like '%FRANCE%'

>>>>>
>>>>>2) REPLACE table1.field3 WITH table2.field4 for table1.field5 = table2.field6
>>>>>
>>>>

>>>>UPDATE T1 set field3 = T2.Field4 from Table1 T1 inner join Table2 T2 on T1.field5 = T2.field6
>>>>

>>>>>3) COUNT TO x FOR table1.field7 = "JOE"
>>>>

>>>>select count(*) from Table1 where Field7 = 'JOE'
>>>>

>>>>
>>>>See also
>>>>
>>>>http://www.w3schools.com/sql/default.asp
>>>>
>>>>and
>>>>
>>>>http://fox.wikis.com/wc.dll?Wiki~VFPSQL-TSQL-Mapping
>>>
>>>
>>>Naomi,
>>>I know that you already know:) Never ever use hardcoded values like that especially when showing to a newcomer. Use parameters. ie:
>>>
>>>Don't do this:
>>>

UPDATE Table1 set field1 = 'HELLO' where Field2 like '%FRANCE%'

>>>
>>>Do like this:
>>>

>>>value1 = 'HELLO'
>>>value2 = '%FRANCE%'
>>>text to m.lcSQLCommand noshow
>>>UPDATE Table1 set field1 = ?m.value1 where Field2 like ?m.value2
>>>endtext

>>>
>>>PS: PMFJI but not using parameters is in top 10 developer mistakes.
>>>Cetin
>>
>>Have you tried something like
>>
>>

UPDATE SET Fields = ?mValue WHERE Field2 = ?SomeLowSelectiveValue AND Field3 = ?SomeHighSelectiveValue

>>
>>And then sometime after it
>>
>>

UPDATE SET Fields = ?mValue WHERE Field2 =  ?SomeHighSelectiveValue AND Field3 = ?SomeLowSelectiveValue

>>
>>You migh get a nasty surprise by the second statement to seem hanging. If we only could give a hint that the second needs to recompile its execution plan, I would always use parameters, but unfortunately, sometimes hardcoding makes some sense.
>>
>>Walter,
>
>Sorry,
>In my book it doesn't makes sense and I have never got such surprises.
>Cetin

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.