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

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

在SELECT語(yǔ)句中，應(yīng)始終將一個(gè)ORDER BY子句與該TOP子句一起使用，以指定哪些行受過TOP濾器影響。如果您需要實(shí)現(xiàn)一個(gè)應(yīng)用程序窗口小部件尋呼解決方案，送塊或數(shù)據(jù)到客戶端的“頁(yè)面”，所以用戶可以通過滾動(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è)表很可能符合聚集索引的順序，但是由于并行性，即使這樣也不能保證。

如果我們超越了查詢單個(gè)表并進(jìn)行一些聯(lián)接，那么“自然”順序就不太明顯了。也許您在AdventureWorks中，只需要五個(gè)客戶，任何五個(gè)客戶及其地址。這樣做是完全合法的，但是如果您隨后忘記執(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é)果，只是查詢返回的前五個(gè)客戶。我得到的順序是Sales.Customer表的聚集索引的順序，該順序是PK_Customer_CustomerID從最低customer_id的人（而不是商店）開始的。不同的執(zhí)行策略可能會(huì)改變這一點(diǎn)。您不能保證確定的結(jié)果。如果您只想在開發(fā)過程中獲取樣本，這可能會(huì)很好，但是在生產(chǎn)系統(tǒng)中，您真正想要的是排名前五位的客戶，并根據(jù)其排名順序確定住址，這些排名由某些屬性（例如花費(fèi)多少）決定。您真的需要那個(gè)ORDER BY。

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

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

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

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

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

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

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

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

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將給您返回五行，但是您可能無(wú)法完全依靠返回的行，盡管它可能按照的順序排列PRIMARY KEY，因?yàn)槲覀冎皇窃谠L問一個(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)生不正確的印象，以為客戶是商店，而大多數(shù)是人！

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

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

這將以隨機(jī)順序返回五行，但需要更多資源才能返回結(jié)果。如果您不是在處理“技巧”表，只是不在乎順序，而需要在代碼中指出這一事實(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

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

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

將TOP與ORDER BY結(jié)合使用以報(bào)告查詢

TOP出于報(bào)告目的而獨(dú)立存在。經(jīng)理們喜歡頂級(jí)客戶和頂級(jí)銷售員的名單。在這一點(diǎn)上，該ORDER BY部分變得至關(guān)重要。

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)最佳的十位銷售人員。

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è)匯總查詢，我們需要前10個(gè)匯總銷售總額，因此我們必須在其上強(qiáng)加一個(gè)訂單。這沒有固定順序傳遞到添加了人員姓名的外部查詢。為了確定外部查詢的順序，它也將需要一個(gè)顯式ORDER BY子句。有時(shí)將其稱為“演示文稿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，您可以提供一種在名人堂中滾動(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í)行類似的操作。

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聲明。我們不能用來(lái)TOP按有意義的時(shí)間順序插入行。正如書中所說(shuō)：

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

如果需要這樣做，則必須TOP與ORDER BY子選擇語(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，則擁有該過濾器有什么意義呢？好吧，實(shí)際上，它可以用在不需要最終以特定順序刪除特定記錄集的情況下。INSERTMERGEUPDATE

如果您需要例如定期從生產(chǎn)系統(tǒng)中刪除許多行，則使用TOP不帶過濾器會(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í)需要訪問該表的系統(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

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

概要

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

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

SQL Prompt 使用教程>>>