Analysis import of MSSQL Indexes

Hi All,

I am accessing several tables from a complex MS SQL Server database for Visual Manufacturing. I am using OLE DB driver as I don't have the Native.

In the MSSQL management console, the table has keys and indexes shown. The indexes have composite keys that I would like to use. When I import the table into the analysis, the indexes are not included as fields.

Also, for one table, one of the keys and one of the indexes have the same name.The key is like an automatic ID and the index is a composite key of 5 non-key fields. The composite key is "clustered".

Many of the tables have the same 5 non-key fields that link them and you can't create a WinDev analysis link with non-key fields.

Any suggestions or links to reference sources would be appreciated. I am a SQL novice.

Jeff

Hi Jeff
I guess it depends on exactly what you need to do! A large part of our work is with MSSQL via OLEDB and we use mainly straight SQL commands & HExecuteSQLQuery or SQLManagerX to access the underlying tables directly. Given that, the analysis is irrelevant as far as normal processing is concerned and in fact, unless we are supplying Reports & Queries with the app we don't bother generating an analysis in Windev at all.

HTH

David

hello

indexes are not in SGBD or more generaly in SQL an existance like windev do with composite key. this indexes are use by the server for optimized acces to data. windev use composite key like a field because it's not a real SGBD in SQL composité key are indexe instead of in windev it's must be a field for acceding the data.

David is right when he say that import analysis in windev could make some mistake.

in SQL, composites keys are indexes and not field. then an analysis with composites keys can't be imported in windev analysis correctly. (or you have the impression that some link are lost but in the server it's not the case because indexes already exists)

regards

Thanks for your replies. That helps me understand the process better.

I am trying to create reports from queries using multiple linked tables that use 5 text fields as the primary link between tables. I edit the join information and add all fields to the join in the query. Is there a better way?

Jeff

David and Firetox,

Can you tell me how to use an existing MSSQL Index?

Thanks,
Jeff

Hi Jeff
The good news is that you probably don't need to do anything. SQL determines what index to use itself, based largely on the WHERE clause. You can force an over-ride of this if you really want to, although it would be extremely rare that the query optimizer would not pick the best index to use. You do of course need to make sure that you have created indexes which are relevant to your query.
If you want to do some fine tuning of indexes there are various SQL commands which tell you what index is being used for a query. The easiest way to use these is with something like MS SQL Server Query Analyzer or, our preferred tool SQL Manager 2005 (the lite version is freeware available from www.sqlmanager.net).

David

Thanks David!

Have a great holiday season.

Jeff