>In the SQL Documentation, it says that SQL Views cannot contain the ORDER BY parameter as well as a few others which are native to the SQL language (at least in VFP). If that is the case, how do you return an ordered view. Do we have to run the view without an order and then build an index for it? Does anyone know if the upsizing wizard that VFP has will automatically create the solution? Seems odd that in saying that views don't allow ORDER BY, that the alternative for how you handle it would not have been explained. Anyway, would appreciate any insight you have to this question.
>
>TIA,
>
>Bill

Hi Bill,

I'm just starting with SQL Server but maybe I can help a little. Oracle, which I am more familiar with works the same way with views. ORDER BY clauses are not allowed. Views assemble fields from several tables. They involve joins across several tables and often from really large data sets. Omitting the ORDER BY helps to optimize the data retrieval by avoiding full table scans. In Oracle at least, indexes can be used in the ORDER BY under very limited, almost never ocurring conditions. To speed retrieval, the order of the results need to be indicated in the WHERE clause even for sql statements that are used outside of a view.

Here's what the man says:

"It is much more efficient to retrieve records via an index than via a manual sort. It is possible to manipulate a SQL statement into fetching rows in the required sequence without having to sort them. This manipulation may involve including a "dummy WHERE" clause as part of the statement.

Here is an example of what goes on behind the scenes when you specify WHERE and ORDER BY clauses. Note that DEPT_TYPE is defined as allowing NULL and has the index over it:

TABLE DEPARTMENT

dept_no NOT NULL PRIMARY KEY
dept_description NOT NULL
dept_type NULL

INDEX dept_type_idx (dept_type)

The following statement fetches rows from the department table in the DEPT_TYPE sequence.

SELECT . . .
FROM department
ORDER BY dept_type

Explain Plan Query Plan
----------------------------------------------------------------
Sort Order By
Table Access FULL

Adding a dummy WHERE clause to the statement triggers an index to be used:

SELECT . . .
FROM department
WHERE dept_type > 0

Explain Plan Query Plan
-----------------------------------------------------------------
Table Access By Rowid on DEPARTMENT
Index Range Scan on Dept_Idx"

The results are returned in the index order. I would guess that the SQL Server query optimizer probably works in a similar way. I used several views in Oracle that I access using remote views in VFP. I have the remote view put the records in the order I need them.

About the upsizing wizard, I haven't a clue, yet.

Gregg