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順に並べたいのですが、そうすると結果が一番大きいユーザーが一番上になります。
この 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を入れてはいけない。そして、もしソートに費用がかかるのであれば、それをサポートするためにインデックスを追加/変更することを検討してもよいでしょう。
を使って、ビューを強制的に順序付けすることに成功しました。
SELECT TOP 9999999 ... ORDER BY something
残念ながら、SELECT TOP 100 PERCENT
を使っても、こちらの問題でうまくいきません。
エラーの内容は、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
.