在今天的文章里，我想談下SQL Server里非常有爭(zhēng)議和復(fù)雜的話題：ORDER BY子句的歧義性。

我們用一個(gè)非常簡(jiǎn)單的SELECT語(yǔ)句開(kāi)始。

1 -- A very simple SELECT statement2 SELECT * FROM Person.Person3 ORDER BY LastName4 GO

從剛才列出的代碼你可以看到，我們只想從Person.Person表以LastName列排序返回記錄。因?yàn)槲覀兿肽鼙M可能簡(jiǎn)單的重用那個(gè)SQL語(yǔ)句，最后我們把它放到視圖里，如下：

1 -- This doesn't work2 CREATE VIEW v_Persons3 AS4     SELECT * FROM Person.Person5     ORDER BY LastName6 GO

但是你會(huì)看到，SQL Server不能創(chuàng)建那個(gè)視圖，只返回一個(gè)錯(cuò)誤信息：

這個(gè)錯(cuò)誤信息告訴你，的那個(gè)你不使用TOP，OFFSET或FOR xml表達(dá)式時(shí)，在視圖里你不允許使用ORDER BY子句?；谀莻€(gè)錯(cuò)誤信息，我們可以通過(guò)增加TOP 100 PERCENT子句到視圖里在輕松修正問(wèn)題。

1 -- Let's make it work!2 CREATE VIEW v_Persons3 AS4     SELECT TOP 100 PERCENT * FROM Person.Person5     ORDER BY LastName6 GO

現(xiàn)在視圖創(chuàng)建沒(méi)有任何問(wèn)題！我們對(duì)視圖執(zhí)行一個(gè)SELECT語(yǔ)句。

1 SELECT * FROM v_Persons2 GO

SELECT語(yǔ)句本身可以執(zhí)行，但當(dāng)你看返回的數(shù)據(jù)時(shí)，瘋狂的事情發(fā)生了：返回的數(shù)據(jù)沒(méi)有按LastName列排序——SQL Server按BusinessEntityID——表上的聚集鍵列排序！

這是SQL Server里的BUG么？不，并不是——它是“故意的”！我們來(lái)解釋下為什么。首先你要知道ORDER BY子句在SQL（編程語(yǔ)言本身）里用2個(gè)不同的上下文：

1. 使用ORDER BY子句你可以定義返回給你客戶(hù)端程序的排序
2. 另外ORDER BY子句用來(lái)定義從TOP表達(dá)式哪些行返回

你必須知道的最重要的事情是，你用視圖定義了所謂的集合（Set），行內(nèi)函數(shù)，派生表，子查詢(xún)和通用表表達(dá)式（common table exPRessions（CTE））。集合是數(shù)學(xué)上的概念，關(guān)系數(shù)據(jù)庫(kù)（例如SQL Server）上集合論（Set Theory）的組成。集合本身是沒(méi)有排序的。因此用視圖定義與ORDER BY組合是不允許的——如你剛才所見(jiàn)。如果你嘗試這樣做，SQL Server不允許你這樣做并給你一個(gè)錯(cuò)誤信息。

當(dāng)然你可以在與TOP表達(dá)式里組合使用ORDER BY。但基本上你在愚弄SQL Server和你自己，因?yàn)?strong>ORDER BY沒(méi)有告訴SQL Server要以怎樣的排序返回?cái)?shù)據(jù)給客戶(hù)端程序。假設(shè)你使用TOP 10 PERCENT。表的前10%是什么？你需要確定性的方式里定義排序。

而且因?yàn)槲覀儽仨毷褂肨OP 100 PERCENT與ORDER BY組合，查詢(xún)優(yōu)化器實(shí)際上在執(zhí)行計(jì)劃里不會(huì)引入排序運(yùn)算符。TOP 100 PERCENT意味著一切，因此如你在下圖所看到的，在執(zhí)行計(jì)劃里TOP運(yùn)算符不需要排序輸入。

在這個(gè)例子里，我們的返回行以從內(nèi)在數(shù)據(jù)結(jié)構(gòu)讀取的排序。這由SQL Server的存儲(chǔ)引擎來(lái)決定返回行的排序。這里我們從聚集索引里讀取行。因此我們拿到的數(shù)據(jù)按BusinessEntityID排序，這是索引列里聚集鍵值。

現(xiàn)在我們修改下視圖定義，從Person.Person表值返回10%的行。我們還是指定了ORDER BY子句。

1 -- Alter the view2 ALTER VIEW v_Persons3 AS4     SELECT TOP 10 PERCENT * FROM Person.Person5     ORDER BY LastName6 GO

當(dāng)你現(xiàn)在看結(jié)果集時(shí)，你會(huì)看到返回的行按LastName列排序的?，F(xiàn)在才對(duì)了，因?yàn)槟阍趫?zhí)行計(jì)劃里看到了排序運(yùn)算符（SQL Server 2014里沒(méi)有出現(xiàn)），因?yàn)門(mén)OP運(yùn)算符最后能返回提供輸入行的前10%的數(shù)據(jù)。

當(dāng)然你可以通過(guò)ORDER BY子句在你引用的視圖里按不同的排序返回10%的行給你的客戶(hù)端程序。

1 SELECT * FROM v_Persons2 ORDER BY FirstName3 GO

現(xiàn)在當(dāng)你看執(zhí)行計(jì)劃時(shí)，你會(huì)在計(jì)劃里看到2個(gè)（SQL Server 2014里只有1個(gè)）。

第1個(gè)（右邊）排序運(yùn)算符為T(mén)OP運(yùn)算符預(yù)排序（返回前10%）。第2個(gè)（左邊）排序運(yùn)算符用來(lái)最后定義的排序，返回給客戶(hù)端程序。當(dāng)你通過(guò)添加TOP 100 PERCENT來(lái)定義的視圖里強(qiáng)制ORDER BY——你基本上就在愚弄SQL Server……

另一個(gè)問(wèn)題是沒(méi)有ORDER BY子句的TOP表達(dá)式不會(huì)提供你確定性的結(jié)果。我們可以用具體的例子演示下這個(gè)問(wèn)題。假設(shè)有下列SELECT語(yǔ)句：

1 SELECT TOP 1 LastName FROM Person.Person2 GO

這個(gè)SQL語(yǔ)句用TOP 1表達(dá)式返回Person.Person表的第一行——沒(méi)有用ORDER BY子句定義排序。這個(gè)排序是基于執(zhí)行計(jì)劃里選擇的索引。在這個(gè)例子里SQL Server返回你“Abbas”給你作為結(jié)果，因?yàn)檫@是執(zhí)行計(jì)劃里查詢(xún)優(yōu)化器選擇非聚集索引里第1條可用記錄。

因此從這個(gè)查詢(xún)返回的第1條記錄取決于執(zhí)行計(jì)劃里選擇的索引。如果現(xiàn)在我們把非聚集索引停用呢。

1 -- Let's deactivate this index2 ALTER INDEX [IX_Person_LastName_FirstName_MiddleName] ON Person.Person3 DISABLE4 GO

然后當(dāng)你再次執(zhí)行剛才的SELECT語(yǔ)句，SQL Server返回你Sánchez值，意味只是在執(zhí)行計(jì)劃里現(xiàn)在選擇的聚集索引的第1條記錄。SQL Server從聚集索引里返回了用BusinessEntityID值為1的第1行。

因此你與非確定性記錄打交道時(shí)：你的結(jié)果取決與執(zhí)行計(jì)劃里選擇的索引！你可以通過(guò)增加ORDER BY子句來(lái)輕松實(shí)現(xiàn)查詢(xún)結(jié)果排序的明確性。在這個(gè)情況下ORDER BY子句為TOP表達(dá)式使記錄確定——這樣話在執(zhí)行計(jì)劃里你會(huì)有Sort（Top N Sort）的運(yùn)算符。

1 SELECT TOP 1 LastName FROM Person.Person2 ORDER BY LastName3 GO

在執(zhí)行計(jì)劃里，SQL Server從哪個(gè)索引讀取行并不重要——Sort（Top N Sort）的運(yùn)算符在執(zhí)行計(jì)劃里會(huì)物理預(yù)排序行，并從它返回第N行——很簡(jiǎn)單，是不是？

在SQL（編程語(yǔ)言本身）里ORDER BY子句并不是一個(gè)最簡(jiǎn)單的概念。如你在這篇文章里所學(xué)的，ORDER BY使用2個(gè)不同的上下文，因此你總要考慮下你要使用哪個(gè)上下文。永遠(yuǎn)不要在視圖定義里增加TOP 100 PERCENT來(lái)愚弄SQL Server和你自己——它不會(huì)在最終的記錄集里體現(xiàn)排序。

感謝關(guān)注！