Saya mencoba membuat tampilan dengan klausa ORDER BY
. Saya telah berhasil membuatnya di SQL Server 2012 SP1, tetapi ketika saya mencoba membuatnya kembali di SQL Server 2008 R2, saya mendapatkan kesalahan ini:
Msg 102, Level 15, State 1, Prosedur TopUsers, Baris 11
Salah sintaksis dekat ' OFFSET'.
Kode untuk membuat tampilan adalah
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
=====================
Ini adalah cuplikan layar dari diagram
Saya ingin mengembalikan pengguna DisplayName
dan UserTotalMarks
dan mengurutkan hasil ini, sehingga pengguna dengan hasil terbesar berada di atas.
Saya tidak yakin apa yang anda pikirkan tentang apa yang dicapai oleh ORDER BY
ini? Bahkan jika Anda melakukan menempatkan ORDER BY
dalam tampilan dengan cara yang legal (misalnya dengan menambahkan klausa TOP
), jika Anda hanya memilih dari tampilan, misalnya SELECT * FROM dbo.TopUsersTest;
tanpa klausa ORDER BY
, SQL Server bebas mengembalikan baris dengan cara yang paling efisien, yang tidak akan selalu sesuai dengan urutan yang Anda harapkan. Ini karena ORDER BY
kelebihan beban, karena mencoba untuk melayani dua tujuan: untuk mengurutkan hasil dan untuk menentukan baris mana yang akan dimasukkan dalam TOP
. Dalam hal ini, TOP
selalu menang (meskipun tergantung pada indeks yang dipilih untuk memindai data, Anda mungkin mengamati bahwa urutan Anda bekerja seperti yang diharapkan - tetapi ini hanya kebetulan).
Untuk mencapai apa yang anda inginkan, anda perlu menambahkan klausa ORDER BY
anda ke kueri yang menarik data dari view, bukan ke kode view itu sendiri.
Jadi kode view anda seharusnya hanya:
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
tidak ada artinya sehingga tidak perlu disertakan.
Untuk mengilustrasikan, dengan menggunakan AdventureWorks2012, berikut ini adalah contohnya:
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;
Hasil:
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
Dan Anda dapat melihat dari rencana eksekusi bahwa TOP
dan ORDER BY
telah benar-benar diabaikan dan dioptimalkan oleh SQL Server:
Tidak ada operator TOP
sama sekali, dan tidak ada pengurutan. SQL Server telah mengoptimalkannya sepenuhnya.
Sekarang, jika Anda mengubah tampilan menjadi ORDER BY SalesID
, Anda akan mendapatkan urutan yang dinyatakan oleh tampilan, tetapi hanya - seperti yang disebutkan sebelumnya - secara kebetulan.
Tetapi jika Anda mengubah kueri luar Anda untuk melakukan ORDER BY
yang Anda inginkan:
SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView
ORDER BY CustomerID;
Anda mendapatkan hasil yang diurutkan seperti yang Anda inginkan:
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
Dan rencana tersebut masih mengoptimalkan TOP
/ ORDER BY
dalam tampilan, tetapi pengurutan ditambahkan (dengan biaya yang tidak sedikit, ingatlah Anda) untuk menyajikan hasil yang diurutkan berdasarkan CustomerID
:
Jadi, moral dari cerita ini, jangan menaruh ORDER BY dalam view. Letakkan ORDER BY dalam query yang mereferensikannya. Dan jika pengurutan itu mahal, Anda mungkin mempertimbangkan untuk menambahkan/mengubah indeks untuk mendukungnya.
Saya telah berhasil memaksa tampilan untuk dipesan menggunakan
SELECT TOP 9999999 ... ORDER BY something
Sayangnya menggunakan SELECT TOP 100 PERSEN
tidak bekerja karena masalah di sini.
Kesalahan adalah: FROM (SELECT empno,nama FROM table1 where lokasi = 'A' ORDER BY emp_no)
Dan solusinya adalah : DARI (SELECT empno,nama FROM table1 where lokasi = 'A') ORDER BY emp_no