SQL Prompt使用教程：使用ORDER BY（PE020）插入永久表

---

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

點擊下載SQL Prompt正式版

絕對不能保證關系表中的數據按特定順序返回，因此將ORDER BY添加到INSERT INTO語句是沒有意義的，并且在SQL Server 2012之前的版本中可能會導致性能問題。如果確實需要對表中的行強加特定順序，請改用Row_Number（）窗口子句。

用于產生插入到永久表中的結果的SQL查詢的順序由一條ORDER BY語句指定。關系表沒有順序，因此ORDER BY沒有意義。Row_Number()如果需要通過查詢對表中的行強加特定順序，請使用window子句，特別是如果需要允許隨后向表中插入時，請使用window子句。對于沒有后續插入的臨時表或表變量，可以通過IDENTITY目標表中的字段來維護當前順序，但是現在已經有了更好，更通用的方法，因此這是不合時宜的。

當插入到永久表中時，SQL Prompt檢測到使用INSERT INTO…ORDER BY時，將違反性能規則（PE020）。

這樣做是為了找出容易犯的錯誤，即留下不再需要的ORDER BY子句。 在SQL Server 2012之前，就經過時間和資源而言，這些ORDER BY子句可能會非常昂貴，但現在優化程序通常會忽略它們。

無意義的訂單

假設我們有清單1中的查詢。

SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName
         + Coalesce(' ' + Person.MiddleName + ' ', ' ') + Person.LastName
         + Coalesce(' ' + Person.Suffix, '') AS FullName
    FROM AdventureWorks2016.Person.Person
    ORDER BY Person.LastName, Person.FirstName;

清單1

結果如下：

好。我們喜歡它及其給出的順序，因此我們嘗試將數據插入工作表中，并保持相同的順序。

IF Object_Id('dbo.OurPeople1', 'U') IS NOT NULL DROP TABLE dbo.OurPeople1;
  CREATE TABLE OurPeople1 (FullName VARCHAR(40) NOT NULL);
  INSERT INTO OurPeople1 (FullName)
    SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName
           + Coalesce(' ' + Person.MiddleName + ' ', ' ') + Person.LastName
           + Coalesce(' ' + Person.Suffix, '') AS FullName
      FROM AdventureWorks2016.Person.Person
      ORDER BY Person.LastName, Person.FirstName;

清單2

現在，我們使用以下命令從表中進行選擇：

SELECT  OurPeople1.FullName FROM dbo.OurPeople1;

清單3

當然，表的內容沒有自然順序，因此清單2中使用ORDER BY是沒有意義的。 如果我們在FullName列上創建了聚簇索引，以將OurPeople1變成表而不是堆，則清單3會得到不同的順序，即聚簇索引的順序。

在此示例中，清單2中的ORDER BY子句已被SQL Server完全忽略。 確保從dbo.OurPeople1表以特定順序返回數據的唯一方法是指定該順序。 如果要按Person.LastName，Person.FirstName排序，則首先在工作表中將排序項（LastName和FirstName）作為列，然后執行…

SELECT  OurPeople1.FullName FROM dbo.OurPeople1
    ORDER BY OurPeople1.LastName, OurPeople1.FirstName;

清單4

您可以添加原始數據的PRIMARY KEY字段，即添加Person.Person表的BusinessEntity_ID，進行連接，然后對這些缺失的字段進行排序，如清單5所示。

IF Object_Id('dbo.OurPeople4', 'U') IS NOT NULL DROP TABLE dbo.OurPeople4;
  CREATE TABLE dbo.OurPeople4 (BusinessEntityid INT NOT NULL, FullName VARCHAR(40) NOT NULL);
  INSERT INTO dbo.OurPeople4 (BusinessEntityid, FullName)
    SELECT Person.BusinessEntityID,
      Coalesce(Person.Title + ' ', '') + Person.FirstName
      + Coalesce(' ' + Person.MiddleName + ' ', ' ') + Person.LastName
      + Coalesce(' ' + Person.Suffix, '')
      FROM AdventureWorks2016.Person.Person;
  /* and when you want an ordered list you do this ... */
  SELECT OurPeople4.FullName
    FROM dbo.OurPeople4
      INNER JOIN AdventureWorks2016.Person.Person AS po
        ON OurPeople4.BusinessEntityid = po.BusinessEntityID
    ORDER BY po.LastName, po.FirstName;

清單5

無論您隨后對數據進行什么更改，這兩種解決方案都可以保留順序。

IDENTITY技巧

但是，有時由于某些原因，您無法引用進行訂購所依據的原始數據，因此您發現需要指定訂單。可以使用數字遞增的替代字段來指定順序，但是如果添加或修改數據以影響排序順序，則會遇到此解決方案的缺點。

在引入Row_Number()窗口功能之前，有一段時間，您可以在工作表中提供數據順序的唯一方法是通過“IDENTITY技巧”。您插入到臨時表中，以ORDER BY子句提供的順序遞增IDENTITY字段。

IF Object_Id('dbo.OurPeople2', 'U') IS NOT NULL DROP TABLE dbo.OurPeople2;
  CREATE TABLE dbo.OurPeople2 (FullName VARCHAR(40) NOT NULL, TheOrder INT NOT null);
  DECLARE @People TABLE (TheIdentityField INT IDENTITY NOT NULL, FullName VARCHAR(40) NOT NULL, TheOrder AS TheIdentityField)
  INSERT INTO @People (FullName)
    SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName
           + Coalesce(' ' + Person.MiddleName + ' ', ' ') + Person.LastName
           + Coalesce(' ' + Person.Suffix, '') AS FullName
      FROM AdventureWorks2016.Person.Person
      ORDER BY Person.LastName, Person.FirstName;
  INSERT INTO dbo.OurPeople2 (FullName, TheOrder)
      SELECT fullName, Theorder FROM @People
  SELECT  OurPeople2.FullName FROM dbo.OurPeople2 ORDER BY TheOrder

清單6

結果是：

這樣好多了，但是為什么將IDENTITY字段放在表變量中呢？為什么不只在目標表中創建它？問題出在插入。IDENTITY字段是不可變的，因此如果沒有這個中間階段，您將無法更改訂單，也無法輕松地進行除訂單開始或結束之外的任何后續插入操作。

使用Row_Number（）窗口函數

如今，我們不需要任何額外的工作：我們根本不需要使用ORDER BY子句，并且如果需要，我們可以隨后更改順序。我們只使用Row_Number()窗口函數。

IF Object_Id('dbo.OurPeople3', 'U') IS NOT NULL DROP TABLE dbo.OurPeople3;
  CREATE TABLE dbo.OurPeople3 (FullName VARCHAR(40) NOT NULL, TheOrder INT NOT NULL);
  INSERT INTO dbo.OurPeople3 (FullName, TheOrder)
    SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName
           + Coalesce(' ' + Person.MiddleName + ' ', ' ') + Person.LastName
           + Coalesce(' ' + Person.Suffix, ''),
      Row_Number() OVER (ORDER BY Person.LastName, Person.FirstName)
      FROM AdventureWorks2016.Person.Person;
  SELECT OurPeople3.FullName FROM dbo.OurPeople3 ORDER BY OurPeople3.TheOrder;

清單7

總結

在這里顯示的所有代碼中，只有清單2觸發了PE020警告。 僅當您使用帶有ORDER BY子句的SELECT語句插入永久表時，才會發生這種情況。 它只是在建議您不必要的ORDER BY子句。 如果您故意在插入時尋求保留特定順序，請在INSERT INTO語句內的SELECT查詢中使用Row_Number（）窗口函數，而不要使用ORDER BY語句。

相關內容推薦：

試用下載>>>

SQL Prompt 使用教程>>>

---

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊