请选择 进入手机版 | 继续访问电脑版

技术控

    今日:0| 主题:61300
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] Avoid ORDER BY in SQL Server views

[复制链接]
シ尊嚴的色調 发表于 2016-10-4 12:57:08
199 4
By:Aaron Bertrand |  |   Related Tips:More >Views
   Problem

  For as long as I have been working with SQL Server, I have watched people add ORDER BY to views for various reasons - most commonly, they are referencing the view in multiple queries, and want to avoid having to repeat an ORDER BY clause in each of those outer queries.
  Solution

   ORDER BY in a view is not something you can rely on. In fact, it's not even something you can accomplish on its own. Let's look at a few examples using the new WideWorldImporters sample database :
  1. USE WideWorldImporters;
  2. GO

  3. CREATE VIEW dbo.CustomersByName
  4. AS
  5.   SELECT CustomerID, CustomerName, DeliveryCityID
  6.   FROM Sales.Customers
  7.   ORDER BY CustomerName;
  8. GO
复制代码
This results in a very explicit error message:
  Msg 1033, Level 15, State 1, Procedure CustomersByName
  Sure, there are kludges to get it in there, most of which I see in the wild are *exactly* this:
  1. SELECT TOP (100) PERCENT <columns>
  2.   FROM dbo.<table>
  3.   ORDER BY <column>;
复制代码
If we do that with our query above:
  1. CREATE VIEW dbo.CustomersByName
  2. AS
  3.   SELECT TOP (100) PERCENT CustomerID, CustomerName, DeliveryCityID
  4.   FROM Sales.Customers
  5.   ORDER BY CustomerName;
  6. GO
复制代码
The view is successfully created, but we can easily see that an outer query against the view, without an ORDER BY, won't obey the ORDER BY from inside the view:
   

Avoid ORDER BY in SQL Server views

Avoid ORDER BY in SQL Server views-1-技术控-sql,server,order,by,sqlserver,order,by,sqlserver中order,by,sql,server,orderby,in,order,to,avoid

  This used to work, back in the SQL Server 2000 days, but not in modern versions. Let's compare the execution plans between a query against the view (which returns data sorted by CustomerID) and running the query *inside* the view directly (which returns data sorted by CustomerName):
  1. SELECT CustomerID, CustomerName, DeliveryCityID
  2.   FROM dbo.CustomersByName;

  3. SELECT TOP (100) PERCENT CustomerID, CustomerName, DeliveryCityID
  4.   FROM Sales.Customers
  5.   ORDER BY CustomerName;
复制代码
The execution plans are virtually identical, except for one little thing (see if you can spot it):
   

Avoid ORDER BY in SQL Server views

Avoid ORDER BY in SQL Server views-2-技术控-sql,server,order,by,sqlserver,order,by,sqlserver中order,by,sql,server,orderby,in,order,to,avoid

  When we run the query from inside the view (without actually referencing the view itself), the results are ordered as we desired, and this is facilitated by that extra Sort operator. This doesn't happen when we query against the view because, essentially, SQL Server looks at our outer query, sees there is no ORDER BY, and says, "they don't care about the order of results," so feels free to return the data in the most efficient order (which happens to be by CustomerID in this example, but that won't always be true, depending on the columns in the query, other indexes, and other factors).
  Since the ORDER BY inside the view is only allowed to exist as a way to determine which TOP rows to include, and since TOP (100) PERCENT means "return all the rows," these are two pieces of logic SQL Server feels confident about completely throwing away and not considering at all. This is why there is no Sort operator in the first plan, and no Top operator in *either* plan.
  The underlying problem here is that ORDER BY can serve these two functions - one to determine TOP inclusion, and one to determine presentation order. There is no way to prioritize these; in the second query above, the ORDER BY is actually serving both functions. Ideally, SQL Server's TOP clause should have been implemented with its own ORDER BY, so that if we wanted to return the first 10 customers alphabetically but have the results sorted by DeliveryCityID, we could say something like this:
  1. SELECT TOP (10) OVER (ORDER BY CustomerName)
  2.     CustomerID, CustomerName, DeliveryCityID
  3.   FROM Sales.Customers
  4.   ORDER BY DeliveryCityID;
复制代码
Instead, we have to write our queries a little more elaborately, where we have to perform the two different functions of ORDER BY in two different stages:
  1. SELECT CustomerID, CustomerName, DeliveryCityID
  2.   FROM
  3.   (
  4.     SELECT TOP (10)
  5.       CustomerID, CustomerName, DeliveryCityID
  6.     FROM Sales.Customers
  7.     ORDER BY CustomerName
  8.   ) AS x
  9.   ORDER BY DeliveryCityID;
复制代码
(A more common example of this is when you want to present, say, a top 10 list ordered from 10 to 1.)
   OFFSET/FETCH syntax was introduced in SQL Server 2012, which seems like it could help to solve the problem, but unfortunately it still uses the same overloaded ORDER BY clause.
   Are there other kludges to get the "desired" behavior?
  Of course. There are always going to be ways to get SQL Server to bend to your will, even if only temporarily - by using a percentage so close to 100 percent that it will round up, or by using the upper bound of the BIGINT type:
  1. CREATE VIEW dbo.CustomersByName_KludgeOne
  2. AS
  3.   SELECT TOP (99.9999999999999999) PERCENT CustomerID, CustomerName, DeliveryCityID
  4.   FROM Sales.Customers
  5.   ORDER BY CustomerName;
  6. GO

  7. CREATE VIEW dbo.CustomersByName_KludgeTwo
  8. AS
  9.   SELECT TOP (9223372036854775807) CustomerID, CustomerName, DeliveryCityID
  10.   FROM Sales.Customers
  11.   ORDER BY CustomerName;
  12. GO
复制代码
Sure enough, if you select from each view without an ORDER BY, the results are ordered by name, and the Sort operator is re-introduced into the plan. You'll also notice a new Top operator that wasn't present before, even though no rows are filtered out in either case.
   But please don't learn from this or rely on it; like the original functionality that was phased away starting in SQL Server 2005, there is always a chance that some future build of SQL Server will "fix" this loophole, and it will stop behaving as you observe. Never mind that even when you *can* get the view to return data in a certain order, this may not be the order desired by all queries that reference the view (whether they do so explicitly or not). SQL Server will not be able to determine how to resolve conflicts or prioritization issues with outer queries that have their own, different, ORDER BY clause. You could guess that the outer ORDER BY will always win, but this isn't documented, so is just as unreliable as the other behavior.
  Summary

  Views are not meant to dictate presentation order; if you expect queries against a view from presenting the data in a predictable order, please stop looking for kludges and add those ORDER BY clauses to your outer queries. Having ORDER BY inside the view is not going to work in all cases, and is going to be confusing for people reading or maintaining the code.
  Next Steps

  See these tips and other resources:
  
       
  • Trick to Optimize TOP Clause in SQL Server   
  • Overview of OFFSET and FETCH Feature of SQL Server 2012   
  • Pagination with OFFSET / FETCH : A better way   
  • Comparing performance for different SQL Server paging methods   
  • TOP (Transact-SQL) (MSDN)   
  • ORDER BY Clause (Transact-SQL) (MSDN)  
   Last Update: 10/4/2016
   

Avoid ORDER BY in SQL Server views

Avoid ORDER BY in SQL Server views-3-技术控-sql,server,order,by,sqlserver,order,by,sqlserver中order,by,sql,server,orderby,in,order,to,avoid

   

Avoid ORDER BY in SQL Server views

Avoid ORDER BY in SQL Server views-4-技术控-sql,server,order,by,sqlserver,order,by,sqlserver中order,by,sql,server,orderby,in,order,to,avoid

  About the author

         

Avoid ORDER BY in SQL Server views

Avoid ORDER BY in SQL Server views-5-技术控-sql,server,order,by,sqlserver,order,by,sqlserver中order,by,sql,server,orderby,in,order,to,avoid
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades , first earning the Microsoft MVP award in 1997.
    View all my tips            Related Resources
   
         
  • More Database Developer Tips...   
维网营销 发表于 2016-10-5 07:44:41
趁今日埋头苦干,免他日仰慕求人.
回复 支持 反对

使用道具 举报

仅存旳依赖╮ 发表于 2016-10-5 08:30:18
兄弟我先抛块砖,有玉的尽管砸过来。
回复 支持 反对

使用道具 举报

忘居 发表于 2016-10-5 18:25:08
占坑编辑ing
回复 支持 反对

使用道具 举报

jefferyc 发表于 2016-11-8 16:31:41
支持,楼下的跟上哈~
回复 支持 反对

使用道具 举报

我要投稿

回页顶回复上一篇下一篇回列表
手机版/c.CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 | 粤公网安备 44010402000842号 )

© 2001-2017 Comsenz Inc.

返回顶部 返回列表