In a SQL 2000 SP 1 installation I've a problem with a view.
When I run a select from this view with a where clause the view return me a result set, but if I insert a order by clause the result set is empty.
I try to re-organize indexes and update statistics without results.
The server is on a client site so I can't update the SQL 2000 installation immediatly, I try to ask about the installation of the SP 3.
But in the meanwhile I need some information. Anyone see this problem before.
Is the SELECT statement that defines the view. It can use more than one table and other views. To select from the objects referenced in the SELECT clause of a view created, it is necessary to have the appropriate permissions.
A view does not have to be a simple subset of the rows and columns of one particular table. A view can be created using more than one table or other views with a SELECT clause of any complexity.
In an indexed view definition, the SELECT statement must be a single table statement or a multitable JOIN with optional aggregation.
There are a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement cannot:
Include COMPUTE or COMPUTE BY clauses.
Include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.
Include the INTO keyword.
Reference a temporary table or a table variable.
Because select_statement uses the SELECT statement, it is valid to use <join_hint> and <table_hint> hints as specified in the FROM clause. For more information, see FROM and SELECT.
Functions can be used in the select_statement.
select_statement can use multiple SELECT statements separated by UNION or UNION ALL.
I suggest you run the ordering query on the view in Query Analyser with Menu->Query->Show Execution Plan enabled, and study the rowcount numbers between the last stages (upper leftmost) of the query.
You may be getting an unintended DISTINCT-ing effect from the order by clause. Usually views are generated and then filtered, but the query optimisier will often bundle the commands outside a view set with those inside a view set because its more efficient to do so.
Also bare in mind UNION merges non-distinct record sets to be distinct, you may actually require UNION ALL to get the result you want.
Check the actual plan and find where the row counts are being reduced to zero.
In attach you find DDL of views and tables involved in the queries.
I'm sorry but they are very complex and not properly clear.
In attach there are also the Execution Plan of the query with only the WHERE clause and the Execution Plan of the query with WHERE and ORDER.
Some other info:
- the second query in the union (v4_pp_fact) in this particular case contains no data (also without the WHERE clause)
- I've run DBCC CHECKDB and there're no error