Using ORDER BY in a SQL Server 2005 or SQL Server 2008 View
Views (2990)
Like
Share
Report
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 a WHERE 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!
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 a WHERE 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!
This was originally posted here.
*This post is locked for comments