SQL Prompt是一款實(shí)用的SQL語(yǔ)法提示工具。SQL Prompt根據(jù)數(shù)據(jù)庫(kù)的對(duì)象名稱(chēng)、語(yǔ)法和代碼片段自動(dòng)進(jìn)行檢索，為用戶(hù)提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開(kāi)發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶(hù)還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt正式版

使用SQL提示查找代碼氣味：SELECT語(yǔ)句中沒(méi)有ORDER BY的TOP（BP006）

在SELECT語(yǔ)句中使用TOP，而沒(méi)有后續(xù)的ORDER BY子句，在SQL Server中是合法的，但沒(méi)有意義，因?yàn)樵?xún)問(wèn)TOP x行意味著保證數(shù)據(jù)按一定順序存在，并且表沒(méi)有隱式邏輯順序。您必須指定順序。

在SELECT語(yǔ)句中，應(yīng)始終將一個(gè)ORDER BY子句與該TOP子句一起使用，以指定哪些行受過(guò)TOP濾器影響。如果您需要實(shí)現(xiàn)一個(gè)應(yīng)用程序窗口小部件尋呼解決方案，送塊或數(shù)據(jù)到客戶(hù)端的“頁(yè)面”，所以用戶(hù)可以通過(guò)滾動(dòng)數(shù)據(jù)，它是更好，更容易使用OFFSET- FETCH節(jié)中ORDER BY節(jié)，而不是TOP條款。

SQL Prompt（BP006）中的“最佳實(shí)踐”代碼分析規(guī)則中包含一個(gè)避免TOP在SELECT語(yǔ)句中使用而不帶的建議。

用TOP限制行

TOP不是標(biāo)準(zhǔn)的SQL，但是很直觀。如果您只想從表源中獲取一些示例行，那么很容易使用TOP不帶ORDER BY子句的關(guān)鍵字。單個(gè)表可能符合聚集索引的順序，但是由于并行性，即使這樣也不能保證。

如果我們超越了查詢(xún)單個(gè)表并進(jìn)行一些聯(lián)接，那么“自然”順序就不太明顯了。也許您在AdventureWorks中，只需要五個(gè)客戶(hù)，任何五個(gè)客戶(hù)及其地址。這樣做是完全合法的，但是如果您隨后忘記執(zhí)行此操作的原因，則有些危險(xiǎn)。

SELECT TOP 5 Person.Title, Person.FirstName, Person.MiddleName,
  Person.LastName, Address.AddressLine1, Address.AddressLine2, Address.City,
  Address.PostalCode, AddressType.Name
  FROM Sales.Customer
    INNER JOIN Person.Person
      ON Customer.PersonID = Person.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress
      ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
    INNER JOIN Person.Address
      ON BusinessEntityAddress.AddressID = Address.AddressID
    INNER JOIN Person.AddressType
      ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;

您將獲得期望的結(jié)果，只是查詢(xún)返回的前五個(gè)客戶(hù)。我得到的順序是Sales.Customer表的聚集索引的順序PK_Customer_CustomerID，從最低customer_id的人（而不是商店）開(kāi)始。不同的執(zhí)行策略可能會(huì)改變這一點(diǎn)。您不能保證確定的結(jié)果。如果您只想在開(kāi)發(fā)過(guò)程中獲取樣本，這可能會(huì)很好，但是在生產(chǎn)系統(tǒng)中，您真正想要的是排名前五位的客戶(hù)，并根據(jù)其排名順序確定地址，這些排名由某些屬性（例如花費(fèi)多少）決定。你真的需要那個(gè)ORDER BY。

簡(jiǎn)而言之，SQL表不能保證其固有順序的一致性。您可能已經(jīng)設(shè)置了a PRIMARY KEY，使您的表值具有一些基本的順序，但這不能保證始終如一。SQL Server保留在創(chuàng)建執(zhí)行計(jì)劃以返回結(jié)果的過(guò)程中引入所需的任何優(yōu)化的權(quán)利，即使這意味著按不同的順序傳遞結(jié)果。簡(jiǎn)而言之，除非您通過(guò)ORDER BY聲明將其明確顯示，否則您不能保證結(jié)果將按預(yù)期的順序返回。

因此，我們回到了完全合理的要求，即開(kāi)發(fā)人員必須能夠查詢(xún)查詢(xún)中代表性的行樣本。應(yīng)該怎么做？

SET ROWCOUNT和TABLESAMPLE：它們有幫助嗎？

曾經(jīng)有一段時(shí)間，我們不得不使用該SET ROWCOUNT語(yǔ)句來(lái)限制返回的行數(shù)。這樣做的一個(gè)缺點(diǎn)是查詢(xún)優(yōu)化器無(wú)法根據(jù)請(qǐng)求的行數(shù)來(lái)創(chuàng)建有效的計(jì)劃，因?yàn)檫@ROWCOUNT是會(huì)話或過(guò)程/觸發(fā)器范圍的設(shè)置，對(duì)于查詢(xún)中的查詢(xún)優(yōu)化器而言不可見(jiàn)。

同樣，有可能忘記您已設(shè)置了ROWCOUNT并且忽略了“取消設(shè)置”它。另一個(gè)缺點(diǎn)是您無(wú)法將值傳遞給變量。TOP更好，因?yàn)樗谡Z(yǔ)句級(jí)別起作用，并且您可以將行值或百分比作為變量或表達(dá)式傳遞。

您可能認(rèn)為您可以使用該TABLESAMPLE子句可靠地從表中獲取有限數(shù)量的行。唯一的問(wèn)題是它不能像廣告中那樣工作，即使它確實(shí)像廣告中那樣工作，也只能在表上工作，而不是各種各樣的表源。

SELECT * FROM Sales.Customer TABLESAMPLE SYSTEM (5);

這應(yīng)該將從FROM子句中的表返回的行數(shù)限制為樣本數(shù)或PERCENT行數(shù)。快速測(cè)試將向您展示為什么沒(méi)人使用它。

DROP TABLE IF EXISTS #Result;
CREATE TABLE #Result (TheOrder INT IDENTITY, TheRowsReturned INT);
GO
INSERT INTO #Result (TheRowsReturned)
  SELECT Count(*) FROM Sales.Customer TABLESAMPLE(200 ROWS); 
GO 30
SELECT #Result.TheOrder, #Result.TheRowsReturned FROM #Result;

使用TOP…ORDER BY獲取有意義的表樣本

到目前為止，由于TABLESAMPLE已損壞，因此從表中獲取樣品的做法有些尷尬。

SELECT TOP 5 * FROM Sales.Customer

為什么這很尷尬？清單4將給您返回五行，但是您不能完全依賴(lài)返回的行，盡管它可能按照的順序排列PRIMARY KEY，因?yàn)槲覀冎辉L問(wèn)一個(gè)表。但是，sales.customer它有點(diǎn)“技巧”表，因?yàn)樗褂昧硕鄳B(tài)關(guān)聯(lián)，并且19820行中的前700個(gè)代表商店，而不是人。因此，清單4可能會(huì)給出該表的一個(gè)非常不具有代表性的示例，因?yàn)槟赡軙?huì)輕易地對(duì)表中的數(shù)據(jù)產(chǎn)生不正確的印象，以為客戶(hù)是商店，而大多數(shù)是人！

大多數(shù)開(kāi)發(fā)人員希望看到的是他們正在調(diào)查的表的幾行，是隨機(jī)抽取的，但是如果您希望以隨機(jī)順序抽取示例，則必須明確說(shuō)明。

SELECT TOP 5 * FROM Sales.Customer ORDER BY NewId()

這將以隨機(jī)順序返回五行，但需要更多資源才能返回結(jié)果。如果您不是在處理“技巧”表，而是只在乎順序，而無(wú)需在代碼中指出這一事實(shí)，則SQL Server將接受任何系統(tǒng)函數(shù)，例如@@version或host_name()，甚至ORDER BY（SELECT NULL）。在SQL Server拒絕需要Windows的窗口函數(shù)中的代碼的情況下，通常會(huì)看到這種技巧ORDER BY。它的意思是“我知道，我知道，但我故意這樣做 ”。

SELECT TOP 10 * FROM Sales.Customer ORDER BY @@identity

如果你很高興與您通過(guò)使用獲得的記錄TOP沒(méi)有ORDER BY，那么最好是完全明確的，并指出，你真的希望它由PRIMARY KEY場(chǎng)

SELECT TOP 5 *
  FROM Sales.Customer
  ORDER BY Customer.CustomerID;

SELECT TOP 10 Person.BusinessEntityID, Sum(SalesOrderHeader.TotalDue) AS expenditure
  FROM Sales.SalesPerson
    INNER JOIN Sales.SalesOrderHeader
      ON SalesPerson.BusinessEntityID = SalesOrderHeader.SalesPersonID
    INNER JOIN Person.Person
      ON SalesPerson.BusinessEntityID = Person.BusinessEntityID
  GROUP BY Person.BusinessEntityID
  ORDER BY Sum(SalesOrderHeader.TotalDue) DESC;

這為您提供了表現(xiàn)最佳的十位銷(xiāo)售人員。

SELECT SalesPerformance.SalesValue,
  Coalesce(Person.Title + ' ', '') + Person.FirstName
  + Coalesce(' ' + Person.MiddleName, '') + ' ' + Person.LastName
  + Coalesce(' ' + Person.Suffix, '') AS SalesPerson
  FROM
    (
    SELECT TOP 10 SalesPerson.BusinessEntityID AS salesPerson,
      Sum(SalesOrderHeader.TotalDue) AS SalesValue
      FROM Sales.SalesPerson
        INNER JOIN Sales.SalesOrderHeader
          ON SalesPerson.BusinessEntityID = SalesOrderHeader.SalesPersonID
        INNER JOIN Person.Person
          ON SalesPerson.BusinessEntityID = Person.BusinessEntityID
      GROUP BY SalesPerson.BusinessEntityID
      ORDER BY Sum(SalesOrderHeader.TotalDue) DESC
    ) AS SalesPerformance(SalesPerson, SalesValue)
    INNER JOIN Person.Person
      ON SalesPerformance.SalesPerson = Person.BusinessEntityID
  ORDER BY SalesPerformance.SalesValue DESC

為什么我們需要第二次ORDER BY呢？原始SQL是一個(gè)匯總查詢(xún)，我們需要前10個(gè)匯總銷(xiāo)售總額，因此我們必須在其上強(qiáng)加一個(gè)訂單。這沒(méi)有固定順序傳遞到添加了人員姓名的外部查詢(xún)。為了確定外部查詢(xún)的順序，它也將需要一個(gè)顯式ORDER BY子句。有時(shí)將其稱(chēng)為“演示ORDER BY”或“演示排序”。

當(dāng)然，更好的方法是ORDER BY將SQL Server 2012及更高版本中的可選OFFSET– FETCH子句與一起使用TOP。它具有更多的用途，并且也是標(biāo)準(zhǔn)的ANSI I SQL。這是AdventureWorks服務(wù)時(shí)間最長(zhǎng)的20名員工。

SELECT Employee.JobTitle, Employee.HireDate,
  Coalesce(Person.Title + ' ', '') + Person.FirstName
  + Coalesce(' ' + Person.MiddleName, '') + ' ' + Person.LastName
  + Coalesce(' ' + Person.Suffix, '') AS Name
  FROM HumanResources.Employee
    INNER JOIN Person.Person
      ON Person.BusinessEntityID = Employee.BusinessEntityID
  ORDER BY Employee.HireDate ASC 
    OFFSET 0 ROWS FETCH FIRST 20 ROWS ONLY;

現(xiàn)在，借助ORDER BY…OFFSET…ROWS FETCH FIRST…ROWS ONLY，您可以提供在整個(gè)名人堂中滾動(dòng)或翻頁(yè)的方法。

將TOP與INSERT，UPDATE，MERGE或DELETE一起使用

不鼓勵(lì)您不使用TOPwith和ORDER BY，這在某些情況下被積極禁止使用，這似乎很奇怪。還有SELECT說(shuō)法，DELETE，INSERT，MERGE和UPDATE語(yǔ)句都有一個(gè)TOP條款。與相比SELECT，您不能有關(guān)聯(lián)ORDER BY子句。讓我們來(lái)看這個(gè)例子。

DROP TABLE IF EXISTS #tempCustomer; --in case it exists
SELECT Customer.CustomerID, Customer.PersonID, Customer.StoreID,
  Customer.TerritoryID, Customer.AccountNumber, Customer.rowguid,
  Customer.ModifiedDate
INTO #tempCustomer
  FROM Sales.Customer --just for the test
 
UPDATE TOP (10) #tempCustomer
  SET #tempCustomer.AccountNumber = 
    Replace(#tempCustomer.AccountNumber, 'AW', 'PF')
OUTPUT Deleted.CustomerID, Deleted.AccountNumber AS before,
  Inserted.AccountNumber AS after 

現(xiàn)在嘗試添加一個(gè)ORDER BY子句！它不會(huì)允許的。如文檔所述：

“在引用的行TOP表達(dá)使用INSERT，UPDATE，MERGE或DELETE不設(shè)置在任何順序”。

您必須執(zhí)行類(lèi)似的操作。

UPDATE #tempCustomer
  SET #tempCustomer.AccountNumber = --
  Replace(#tempCustomer.AccountNumber, 'AW', 'PF')
OUTPUT Deleted.CustomerID, Deleted.AccountNumber AS before,
  Inserted.AccountNumber AS AFTER
  FROM
    (
    SELECT TOP 10 CustomerID
      FROM #tempCustomer
      ORDER BY #tempCustomer.CustomerID DESC
    ) AS ordered
  WHERE #tempCustomer.CustomerID = ordered.CustomerID
  GO  

同樣，INSERT聲明。我們不能使用TOP按有意義的時(shí)間順序插入行。正如書(shū)中所說(shuō)：

“ TOP與一起使用時(shí)INSERT，引用的行未按任何順序排列，并且該ORDER BY子句無(wú)法在此語(yǔ)句中直接指定。”

如果需要這樣做，則必須TOP與ORDER BY在sub-select語(yǔ)句中指定的子句一起使用。

DELETE有一個(gè)TOP子句，但我們也不能使用它。如果您想清除舊的采購(gòu)訂單明細(xì)怎么辦？您需要確定首先清除最舊的。我們不能ORDER BY在delete語(yǔ)句中放入，但是我們不必這樣做。

讓我們?cè)O(shè)置測(cè)試。

DROP TABLE IF EXISTS #tempPurchaseOrderDetail; --in case it exists
SELECT POD.PurchaseOrderID, POD.PurchaseOrderDetailID, POD.DueDate,
  POD.OrderQty, POD.ProductID, POD.UnitPrice, POD.LineTotal, POD.ReceivedQty,
  POD.RejectedQty, POD.StockedQty, POD.ModifiedDate
INTO #tempPurchaseOrderDetail
  FROM Purchasing.PurchaseOrderDetail AS POD

現(xiàn)在，我們刪除十個(gè)最舊的采購(gòu)訂單明細(xì)。

DELETE FROM #tempPurchaseOrderDetail
OUTPUT Deleted.DueDate, Deleted.LineTotal, Deleted.PurchaseOrderID
  WHERE PurchaseOrderDetailID IN
          (
          SELECT TOP 10 PurchaseOrderDetailID
            FROM #tempPurchaseOrderDetail
            ORDER BY DueDate ASC
          );
GO

那么，TOP如果不能使用，或DELETE，則擁有該過(guò)濾器有什么意義呢？好吧，實(shí)際上，它可以用在不需要最終以特定順序刪除特定記錄集的情況下。INSERTMERGEUPDATE

如果您需要例如定期從生產(chǎn)系統(tǒng)中刪除許多行，則使用TOP不帶過(guò)濾器會(huì)ORDER BY節(jié)省生命。刪除將被記錄，并且還可能導(dǎo)致鎖升級(jí)。我曾經(jīng)不得不設(shè)計(jì)一個(gè)系統(tǒng)，該系統(tǒng)定期從SQL Server數(shù)據(jù)庫(kù)中清除一百萬(wàn)行。最佳方法是在吃大象時(shí)要連續(xù)咬很多東西，而不是一口吃下去。

我們可以很容易地說(shuō)明這一點(diǎn)，盡管在擁有一個(gè)工作系統(tǒng)之前，您將看不到它的優(yōu)勢(shì)，尤其是在刪除，更新，插入或合并時(shí)需要訪問(wèn)該表的系統(tǒng)。同樣，我們將使用臨時(shí)表來(lái)說(shuō)明這一點(diǎn)，以免干擾AdventureWorks的正常運(yùn)行。

DROP TABLE IF EXISTS #tempPurchaseOrderDetail; --in case it exists
SELECT POD.PurchaseOrderID, POD.PurchaseOrderDetailID, POD.DueDate,
  POD.OrderQty, POD.ProductID, POD.UnitPrice, POD.LineTotal, POD.ReceivedQty,
  POD.RejectedQty, POD.StockedQty, POD.ModifiedDate
INTO #tempPurchaseOrderDetail
  FROM Purchasing.PurchaseOrderDetail AS POD
 
--we delete rows successively
DECLARE @rowcount INT = 1
WHILE @rowcount > 0
  BEGIN
    DELETE TOP (200) FROM #tempPurchaseOrderDetail
      WHERE #tempPurchaseOrderDetail.DueDate < DateAdd(YEAR, -2, GetDate()) SELECT @rowcount = @@RowCount END

過(guò)去，我發(fā)現(xiàn)像這樣的大規(guī)模操作通常會(huì)受益于分塊執(zhí)行，而分塊的大小是與操作系統(tǒng)進(jìn)行微調(diào)以使其正確的問(wèn)題。對(duì)于這樣的工作，在TOP沒(méi)有條款ORDER BY中DELETE，INSERT或UPDATE可以做大規(guī)模的變化，在短時(shí)間內(nèi)一步非常有價(jià)值的，在硬盤(pán)工作的事務(wù)處理系統(tǒng)

摘要

TOP語(yǔ)句中的SQL Server 子句SELECT非常有用且直觀，但是它允許您省去相關(guān)聯(lián)的ORDER BY子句，以闡明您的想法：TOP從哪個(gè)方面看？畢竟，您的TOP十首歌不是最響亮的十首歌，也不是聲音最高的十首歌。就唱片銷(xiāo)量而言，它們是十大最受歡迎的唱片。您可能會(huì)偶然地在開(kāi)發(fā)工作中獲得正確的結(jié)果，但是在生產(chǎn)中，工作負(fù)載，服務(wù)器和數(shù)據(jù)大小可能會(huì)導(dǎo)致查詢(xún)以非常不同的方式進(jìn)行優(yōu)化，從而產(chǎn)生不同的結(jié)果。

對(duì)于處理這類(lèi)事情的更為通用的方式，我建議使用ORDER BY... OFFSET... FETCH在SQL Server 2012中引入的語(yǔ)法，因?yàn)樗歉鼮殪`活和符合性。記住，也比TOP過(guò)濾器難得多。

SQL Prompt 使用教程>>>