How to set an index key for a table (cursor adapter ) on SQL-Server ?

look at this too:

select * from 
(
	select employeeid,employeename,managerid,row_number() over (order by employeename) as keyField from dbo.tblTest
) as t order by keyField

                                              

1:
2:
3:
4:

It is just SQLEXEC(conn_value, "select * from Mytable order by fieldxxx")  which uses index generated on SQL (if it exists). And it is the intention of SQL indexes to use them remotely this way. If you need different index order of one table at the same time then you have to create several cursor adapters each one having different ORDER BY clause...

All other ways can use indexes created in VFP only.

Cursor adapter behaves as native VFP cursor which cannot use SQL indexes directly. But you can create indexes yourself after cursor is created or filled, e.g. in AfterCursoFill event.

You have to explicitly write standard VFP INDEX command:

INDEX ON YourColumn TAG YourTagName1
INDEX ON YourColumn2 TAG YourTagName2

and use it as standard VFP index, i.e. SET ORDER TO YourTagName1

More sophisticated way could read index definitions from SQL Server and create appropriate tags on VFP cursors but you'll still need their names hardcoded in VFP app code.

you actually mentioned the word INDEX but I was thinking you are doing that for getting key value of index which is not possible in SQL Server, however you can have Row_Number to get key of each row based on the column(s) you decide.

If you want to create index to get good performance on SELECT statement or physically arrange the rows than kindly follow below given links.

http://www.exforsys.com/tutorials/sql-server-2005/sql-server-defining-indexes.html

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1128437,00.html

And some more (theoretical) sentences: Cursor adapter moves VFP usage to the client role. Clients should retrieve and use just data necessary for immediate processing. This data retrieval should use indexes created on the server and their usage at client side should not require additional indexing.

Welcome in VFP world RiteshShah!

I would recommend some publications from Hentzenwerke: http://www.hentzenwerke.com/

Seriously, I am sure you'll find many useful features which are still missing (or too complex to implement) on SQL Server. To have both Front and Back-end in one box is not as bad.

I have used fox pro approx 10 years back for sometime only. :) I was impressed at that time too but with the market move, I stopped with SQL Server. BTW, site looks good.

Yes, I am moving to .NET and SQL also (forced by circumstances).

Hello dear experts/RiteshShah:,


Many many thanks for the comments and the solutions.My problem is as follws:
I have created for tabke_one a few indexes on SQL Server 2005 and use this table as a cursor adapter in programm. But i dont know how to use these indexes from in programm written in Visual FoxPro  9 !!!!
For example i have ca_one and it uses the table table_one, i have created 4 indexes on SQL Server 2005 and want to set the ordes . For example i want to say:
select  table_one
set order to index_1 or index_2  ...etc.

many many thanks in advance....

Did you read the answer ID:25299143 ?

You may use indexes created on SQL Server same way as you are using them on SQL Server only. Means in ORDER BY clauses and JOINs.

SET ORDER TO  is FoxPro command and it is not applicable to SQL indexes. To use this command you have to create your own (new) indexes in FoxPro environment:

SELECT table_one
INDEX ON <IndexExpression1> TAG index_1
INDEX ON <IndexExpression2> TAG index_2 ADDITIVE
INDEX ON <IndexExpression3> TAG index_3 ADDITIVE
INDEX ON <IndexExpression4> TAG index_4 ADDITIVE
SET ORDER TO TAG index_1

Hello dear pcelba:

many many thanks for your guide and help. I mean, how can i use the keys ( i mean the generated indexes ) on the SQL server for a certain table ?
A table named "youth" on the SQL Server has a few index keys. Is it possible ( without generating new indexes --using INDEX ON <IndexExpression1> TAG index_1---)  to use the existing keys on SQL server from FoxPro programm?

many many many thanks in advance

Hello dear pcelba,

How can i read the index expressions ( index definition) in my foxpro programm  from SQL- Server into an array or a variable)???

Would you help me please?

Thanks