ORDER BY句を持つビューの作成

ORDER BY」句を持つビューを作成しようとしています。SQL Server 2012 SP1では正常に作成できましたが、SQL Server 2008 R2で再作成しようとすると、次のようなエラーが発生します。

Msg 102, Level 15, State 1, Procedure TopUsers, Line 11
Incorrect 'OFFSET'付近の構文です。

ビューを作成するコードは

CREATE View [dbo].[TopUsersTest] 
as 
select 
u.[DisplayName]  , sum(a.AnswerMark) as Marks
From Users_Questions us inner join [dbo].[Users] u
on u.[UserID] = us.[UserID] 
inner join [dbo].[Answers] a
on a.[AnswerID] = us.[AnswerID]
group by [DisplayName] 
order by Marks desc
OFFSET 0 ROWS

=====================

この図のスクリーンショットです

ユーザーのDisplayNameとUserTotalMarksを返して、この結果をdesc順に並べたいのですが、そうすると結果が一番大きいユーザーが一番上になります。

Aaron Bertrand
編集された質問 3日 3月 2013 в 5:33
プログラミング
database view sql-server tsql sql-server-2008-r2
3日 3月 2013 в 4:16
13 ビュー
ソリューション

この ORDER BY が何を成し遂げようとしているのか、私にはよくわかりません。ビューに ORDER BY を合法的な方法で（例えば TOP 句を追加して）入れたとしても、ORDER BY 句を入れずに SELECT * FROM dbo.TopUsersTest; などのように単にビューから選択した場合、SQL Server は最も効率的な方法で行を返すことができますが、それは必ずしもあなたが期待する順序とは一致しません。これはORDER BYがオーバーロードされているためで、結果をソートすることと、TOPに含める行を指定するという2つの目的を果たそうとしています。この場合、常に TOP が優先されます (ただし、データをスキャンする際に選択したインデックスによっては、期待した通りの順序になっていることもありますが、これは単なる偶然です)。

**目的を達成するためには、ビュー自体のコードにではなく、ビューからデータを引き出すクエリにORDER BY句を追加する必要があります。

つまり、ビューのコードは次のようにすればいいのです。

CREATE VIEW [dbo].[TopUsersTest] 
AS 
  SELECT 
    u.[DisplayName], SUM(a.AnswerMark) AS Marks
  FROM
    dbo.Users_Questions AS uq
    INNER JOIN [dbo].[Users] AS u
      ON u.[UserID] = us.[UserID] 
    INNER JOIN [dbo].[Answers] AS a
      ON a.[AnswerID] = uq.[AnswerID]
    GROUP BY u.[DisplayName];

ORDER BY」は意味がないので、入れてはいけません。

例として、AdventureWorks2012を使って説明します。

CREATE VIEW dbo.SillyView
AS
  SELECT TOP 100 PERCENT 
    SalesOrderID, OrderDate, CustomerID , AccountNumber, TotalDue
  FROM Sales.SalesOrderHeader
  ORDER BY CustomerID;
GO

SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView;

結果です。

SalesOrderID   OrderDate   CustomerID   AccountNumber   TotalDue
------------   ----------  ----------   --------------  ----------
43659          2005-07-01  29825        10-4020-000676  23153.2339
43660          2005-07-01  29672        10-4020-000117  1457.3288
43661          2005-07-01  29734        10-4020-000442  36865.8012
43662          2005-07-01  29994        10-4020-000227  32474.9324
43663          2005-07-01  29565        10-4020-000510  472.3108

実行プランを見ると、TOPとORDER BYがSQL Serverによって完全に無視され、最適化されていることがわかります。

.

TOP "演算子は一切ありませんし、ソートもありません。SQL Serverはこれらを完全に最適化してしまいました。

さて、ビューを ORDER BY SalesID と変更すると、たまたまビューが指定した順序になりますが、これは前述のように偶然の産物です。

しかし、外側のクエリを変更して、望んでいた ORDER BY を実行すると、次のようになります。

SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView
ORDER BY CustomerID;

希望通りの順序で結果が得られます。

SalesOrderID   OrderDate   CustomerID   AccountNumber   TotalDue
------------   ----------  ----------   --------------  ----------
43793          2005-07-22  11000        10-4030-011000  3756.989
51522          2007-07-22  11000        10-4030-011000  2587.8769
57418          2007-11-04  11000        10-4030-011000  2770.2682
51493          2007-07-20  11001        10-4030-011001  2674.0227
43767          2005-07-18  11001        10-4030-011001  3729.364

このプランでは、ビューの TOP/ORDER BY はまだ最適化されていませんが、CustomerID で並べられた結果を表示するために、ソートが (少なからずコストをかけて) 追加されています。

.

つまり、この話の教訓は、 **ビューにORDER BYを入れてはいけない。そして、もしソートに費用がかかるのであれば、それをサポートするためにインデックスを追加/変更することを検討してもよいでしょう。

解説 (10)

を使って、ビューを強制的に順序付けすることに成功しました。

SELECT TOP 9999999 ... ORDER BY something

残念ながら、SELECT TOP 100 PERCENTを使っても、こちらの問題でうまくいきません。

Community
編集した答え 23日 5月 2017 в 12:34
解説 (2)

エラーの内容は、FROM (SELECT empno,name FROM table1 where location = 'A' ORDER BY emp_no)です。

解決策は次のとおりです。FROM (SELECT empno,name FROM table1 where location = 'A') ORDER BY emp_no.

Hamad
編集した答え 11日 12月 2013 в 7:32
解説 (0)