4

My DBA is telling me that it's impossible to have an OrderBy in a SQL view.

I'm having trouble with a 3rd party component that needs to have a view in a particular order. Is this true? Am I not able to sort within a view?

makerofthings7
  • 60,103
  • 53
  • 215
  • 448
  • 3
    A "3rd party component that needs to have a view in a particular order" is nasty. – Tony Nov 19 '10 at 17:42

4 Answers4

9

SQL Server views do not honor order bys. There are hacks* to get them to work, but they are undocumented and I'd suggest not depending on them to work correctly in future revisions.

If you want to order a view the correct method for doing so is to put an order by in the select which is reading the view.

This is a commonly misunderstood point of views. So, references: An article, MSDN.

*- order bys are support with the top clause. So, you could, in theory, do a select top 100%. This is wrong. The server does NOT guarantee that the view will remain ordered. This is only supported so you can properly specify exactly which top rows to include. For some queries the order by be kept, but it is entirely a fluke, and if you depend on it you'll have fun tracking down the bug that will eventually pop up when the order is not kept. Ordering of views is not guaranteed.

Donnie
  • 45,732
  • 10
  • 64
  • 86
  • I'm looking at the "with ties" option as well. This may solve the issue in the short term, but my ASP.NET object should really issue and order by statement. I'll see what I can do. – makerofthings7 Nov 19 '10 at 18:47
4

Ordinarily, SQL Server will object to an ORDER BY in a view. You can cheat a bit by including a TOP clause on your query, which will then allow the ORDER BY.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 1
    +1: The exact syntax is `SELECT TOP (100) PERCENT ...` and is actually inserted automatically when defining a view with an ORDER BY. Now, the debate as to the need for an ORDER BY within a view is another question... :) – Tony Nov 19 '10 at 17:41
  • See my answer above. This doesn't do what you think it does. – Donnie Nov 19 '10 at 17:46
  • @Donnie: Thanks. I learned something new today, although I must admit I've never seen this not work in practical use. Curious to see if I can construct an example where it fails. – Joe Stefanelli Nov 19 '10 at 19:49
0

I would also say: Of cause you can sort select from a view. But can somebody explain what i find here on msdn?:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Jan
  • 15,802
  • 5
  • 35
  • 59
0

Your DBA is probably worried that you will need or want an indexed view, which slows down inserts and updates.

Mark SQLDev
  • 539
  • 3
  • 6