5

I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express 2008 R2).

The basic idea is like this (simplified):

SELECT [EventType].Name, 
    [EventType].TotalOccurrences, 
    [Session].ID, 
    [Session].TotalOccurrences, 
    [Session].TotalOccurrences / [EventType].TotalOccurrences AS saturation
FROM [EventType]
    INNER JOIN [Session] ON [EventType].ID = [Session].Event
ORDER BY saturation

This view will never be called without a where clause though (which in my case would almost return the entire database). It will always be with WHERE [EventType].ID = x. However as soon as I add the where clause, the ORDER BY clause is ignored! Without the where clause it is working as expected.

Actually a different question but I'm throwing it in here while I'm at it, is there a better way to retrieve such info? The [EventType].Name and [EventType].TotalOccurrences will be identical for all returned rows, which is a small waist of network bandwidth. Not that that is a problem, but I'm wondering if anything exists to tackle that without the latency overhead of multiple round-trips to the database?

[Edit] The above sample was over-simplified, the calculated field has other factors so that it would return a different order than simply ORDER BY [Session].TotalOccurrences as it would in this example, but the principle should be clear.

[Edit2] From the answer I come to the conclusion that the Graphical Designer has more features than the underlying database:

Bugus features in SSMS

1

1 Answer 1

11

The query you posted is not valid for creating a view; running CREATE VIEW xy AS for this query will result in an error. Are you using a TOP clause?

A view, being a table expression (a set), can't have the order defined, since that would be against the principles of a relational model (there is no order for rows in a relational table - a set is an unordered collection of tuples). Same goes for other table expressions - derived tables, CTEs etc.

From BOL article about the ORDER BY clause:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Long story short: Use the ORDER BY clause in the outer query that references the view. Do not use it in a view. Even using it with TOP(100) PERCENT (or on SQL Server 2012, the OFFSET-FETCH equivalent) does not guarantee presentation order, it just means you'll get the top 100% of the rows, in any order.

5
  • 1
    +1 Also relevant TOP 100 Percent ORDER BY Considered Harmful. Jun 1, 2014 at 23:56
  • 2
  • Actually I used the Designer in Management Studio, which indeed added TOP (100) PERCENT to the start. Strange that they added the Sort Type and Sort Order columns to the designer while they actually should not be used... By the way, if I add '= @input' to the filter column I get a warning and cannot save the view (@input needs to be defined). I can test it though (also seems crazy to me). Probably I'd rather make this a stored procedure If I want any performance benefit (cached execution plan)? I'm kind of losing the practical use for a view now, is there any? Jun 2, 2014 at 21:58
  • 1
    The TOP (100) PERCENT being suggested by the view designer was actually reported as a bug on Connect, it's even marked as fixed... well, seems it's not fixed. Concerning the usage of an input parameter - can't do that in a view. Closest thing to a "parameterized view" is an inline table-valued function (ITVF) - it's still a table expressions, but it can use input parameters. Jun 2, 2014 at 23:52
  • 1
    About view usage, see Scenarios for Using Views. There are also a couple of SO questions already about that subject. Jun 3, 2014 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.