SQL Prompt是一款實用的SQL語法提示工具。SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據需要進行自定義，使之以預想的方式工作。

點擊下載SQL Prompt正式版

使用SQL提示查找代碼氣味：SELECT語句中沒有ORDER BY的TOP（BP006）

在SELECT語句中使用TOP，而沒有后續的ORDER BY子句，在SQL Server中是合法的，但沒有意義，因為詢問TOP x行意味著保證數據按一定順序存在，并且表沒有隱式邏輯順序。您必須指定順序。

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

SQL Prompt（BP006）中的“最佳實踐”代碼分析規則中包含一個避免TOP在SELECT語句中使用而不帶的建議。

用TOP限制行

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

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

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;

您將獲得期望的結果，只是查詢返回的前五個客戶。我得到的順序是Sales.Customer表的聚集索引的順序PK_Customer_CustomerID，從最低customer_id的人（而不是商店）開始。不同的執行策略可能會改變這一點。您不能保證確定的結果。如果您只想在開發過程中獲取樣本，這可能會很好，但是在生產系統中，您真正想要的是排名前五位的客戶，并根據其排名順序確定地址，這些排名由某些屬性（例如花費多少）決定。你真的需要那個ORDER BY。

簡而言之，SQL表不能保證其固有順序的一致性。您可能已經設置了a PRIMARY KEY，使您的表值具有一些基本的順序，但這不能保證始終如一。SQL Server保留在創建執行計劃以返回結果的過程中引入所需的任何優化的權利，即使這意味著按不同的順序傳遞結果。簡而言之，除非您通過ORDER BY聲明將其明確顯示，否則您不能保證結果將按預期的順序返回。

因此，我們回到了完全合理的要求，即開發人員必須能夠查詢查詢中代表性的行樣本。應該怎么做？

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

曾經有一段時間，我們不得不使用該SET ROWCOUNT語句來限制返回的行數。這樣做的一個缺點是查詢優化器無法根據請求的行數來創建有效的計劃，因為這ROWCOUNT是會話或過程/觸發器范圍的設置，對于查詢中的查詢優化器而言不可見。

同樣，有可能忘記您已設置了ROWCOUNT并且忽略了“取消設置”它。另一個缺點是您無法將值傳遞給變量。TOP更好，因為它在語句級別起作用，并且您可以將行值或百分比作為變量或表達式傳遞。

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

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

這應該將從FROM子句中的表返回的行數限制為樣本數或PERCENT行數。快速測試將向您展示為什么沒人使用它。

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將給您返回五行，但是您不能完全依賴返回的行，盡管它可能按照的順序排列PRIMARY KEY，因為我們只訪問一個表。但是，sales.customer它有點“技巧”表，因為它使用了多態關聯，并且19820行中的前700個代表商店，而不是人。因此，清單4可能會給出該表的一個非常不具有代表性的示例，因為您可能會輕易地對表中的數據產生不正確的印象，以為客戶是商店，而大多數是人！

大多數開發人員希望看到的是他們正在調查的表的幾行，是隨機抽取的，但是如果您希望以隨機順序抽取示例，則必須明確說明。

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

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

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

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

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;

這為您提供了表現最佳的十位銷售人員。

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是一個匯總查詢，我們需要前10個匯總銷售總額，因此我們必須在其上強加一個訂單。這沒有固定順序傳遞到添加了人員姓名的外部查詢。為了確定外部查詢的順序，它也將需要一個顯式ORDER BY子句。有時將其稱為“演示ORDER BY”或“演示排序”。

當然，更好的方法是ORDER BY將SQL Server 2012及更高版本中的可選OFFSET– FETCH子句與一起使用TOP。它具有更多的用途，并且也是標準的ANSI I SQL。這是AdventureWorks服務時間最長的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;

現在，借助ORDER BY…OFFSET…ROWS FETCH FIRST…ROWS ONLY，您可以提供在整個名人堂中滾動或翻頁的方法。

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

不鼓勵您不使用TOPwith和ORDER BY，這在某些情況下被積極禁止使用，這似乎很奇怪。還有SELECT說法，DELETE，INSERT，MERGE和UPDATE語句都有一個TOP條款。與相比SELECT，您不能有關聯ORDER BY子句。讓我們來看這個例子。

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 

現在嘗試添加一個ORDER BY子句！它不會允許的。如文檔所述：

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

您必須執行類似的操作。

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按有意義的時間順序插入行。正如書中所說：

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

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

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

讓我們設置測試。

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

現在，我們刪除十個最舊的采購訂單明細。

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，則擁有該過濾器有什么意義呢？好吧，實際上，它可以用在不需要最終以特定順序刪除特定記錄集的情況下。INSERTMERGEUPDATE

如果您需要例如定期從生產系統中刪除許多行，則使用TOP不帶過濾器會ORDER BY節省生命。刪除將被記錄，并且還可能導致鎖升級。我曾經不得不設計一個系統，該系統定期從SQL Server數據庫中清除一百萬行。最佳方法是在吃大象時要連續咬很多東西，而不是一口吃下去。

我們可以很容易地說明這一點，盡管在擁有一個工作系統之前，您將看不到它的優勢，尤其是在刪除，更新，插入或合并時需要訪問該表的系統。同樣，我們將使用臨時表來說明這一點，以免干擾AdventureWorks的正常運行。

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

過去，我發現像這樣的大規模操作通常會受益于分塊執行，而分塊的大小是與操作系統進行微調以使其正確的問題。對于這樣的工作，在TOP沒有條款ORDER BY中DELETE，INSERT或UPDATE可以做大規模的變化，在短時間內一步非常有價值的，在硬盤工作的事務處理系統

摘要

TOP語句中的SQL Server 子句SELECT非常有用且直觀，但是它允許您省去相關聯的ORDER BY子句，以闡明您的想法：TOP從哪個方面看？畢竟，您的TOP十首歌不是最響亮的十首歌，也不是聲音最高的十首歌。就唱片銷量而言，它們是十大最受歡迎的唱片。您可能會偶然地在開發工作中獲得正確的結果，但是在生產中，工作負載，服務器和數據大小可能會導致查詢以非常不同的方式進行優化，從而產生不同的結果。

對于處理這類事情的更為通用的方式，我建議使用ORDER BY... OFFSET... FETCH在SQL Server 2012中引入的語法，因為它是更為靈活和符合性。記住，也比TOP過濾器難得多。

SQL Prompt 使用教程>>>