Possible to have an OrderBy in a view?

Question

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?

Answer 1

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.