If you are a fan of SQL Server Views, you probably eventually noticed a change that occurred in SQL Server 2005: Views no longer honored the ORDER BY clause.
With SQL Server 2000, ORDER BY clauses were 'sort of' honored, as you could use the TOP 100 PERCENT clause to pacify SQL 2000 and get your results in the requested order.
But with SQL Server 2005, the TOP 100 PERCENT clause no longer worked. SQL purists would claim that SQL Views should not be ordered, and technically, I can understand that argument.
But I don't care about those technical or purist arguments. I use SQL Server as a business tool, whose goal is to deliver data to business users and business owners in any form they want--not what is technically correct according to database engine developers. If business owners want the data upside down and backwards, I need tools that help me get the job done.
Anyway, I'm currently working on a view that will provide a list of inventory items. I won't be able to control how the client queries the view, so I can't control whether they add an ORDER BY clause on the view. If they use Excel to query the view, I can pretty much guarantee that they won't be manually customizing the query, so I have no way of knowing how the data will be sorted once it gets into Excel.
Anyone who has done reporting for an accountant knows that arbitrary, random, and unpredictable are not three of their favorite words. Therefore, I want to control how the data is sorted by the view, and know what my client is going to see when they query the view.
Looking into this issue after many years, I stumbled across this thread on the Microsoft SQL Server forum. One of the participants posted a very interesting variant of the TOP 100 PERCENT clause that apparently tricks SQL Server 2005 into honoring the ORDER BY clause in a view.
The trick is to use a TOP # statement with a specific, but very large number. He recommends just using the maximum integer value, which would be TOP 2147483647.
Sure enough, this works like a charm, and I can now ensure that the results of my SQL Server 2005 view are ordered any way that I would like (well, I'm still working on the upside down request).
There is a potential question of whether this approach impacts performance. For very large result sets or very complex queries, I suppose it could, but for Dynamics GP queries that are written properly, I very rarely have to worry about performance.
I just tested this technique with SQL Server 2008, and it appears to still work.
Go forth and sort!
One thing you can try that may be more reliable and perform better is to create your query as a Table Function (imagine a stored procedure that you execute by SELECTing the resulting columns). You can then create a view that pulls from the table function, which in itself can be sorted.
ReplyDeleteI've actually started using table functions extensively in reporting as they are incredibly flexible as building blocks.
A quick and dirty reference for creating a table function:
http://www.adp-gmbh.ch/sqlserver/tsql/table_func.html
Just a quick update on my last comment - if you use the "Inline Table" method described in the link, you cannot use an ORDER BY, just like you can't in a view.
ReplyDeleteYou will need to use the "Multistatement Table" method and either define a clustered primary key on the return table (good if your ordering columns are unique, and will greatly improve performance if you use these columns in a join), or you will need to use an ORDER BY clause on the INSERT statement that populates the return table.
Thanks for the suggestion, I have never used Table Functions.
ReplyDeleteThe ability to put an index on the resulting table is appealing, especially for joins.