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

---

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

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

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

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

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

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

無意義的訂單

假設(shè)我們有清單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

結(jié)果如下：

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

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

現(xiàn)在，我們使用以下命令從表中進(jìn)行選擇：

SELECT  OurPeople1.FullName FROM dbo.OurPeople1;

清單3

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

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

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

清單4

您可以添加原始數(shù)據(jù)的PRIMARY KEY字段，即添加Person.Person表的BusinessEntity_ID，進(jìn)行連接，然后對這些缺失的字段進(jìn)行排序，如清單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

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

IDENTITY技巧

但是，有時由于某些原因，您無法引用進(jìn)行訂購所依據(jù)的原始數(shù)據(jù)，因此您發(fā)現(xiàn)需要指定訂單?？梢允褂脭?shù)字遞增的替代字段來指定順序，但是如果添加或修改數(shù)據(jù)以影響排序順序，則會遇到此解決方案的缺點(diǎn)。

在引入Row_Number()窗口功能之前，有一段時間，您可以在工作表中提供數(shù)據(jù)順序的唯一方法是通過“IDENTITY技巧”。您插入到臨時表中，以O(shè)RDER 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

結(jié)果是：

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

使用Row_Number（）窗口函數(shù)

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

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

總結(jié)

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

相關(guān)內(nèi)容推薦：

試用下載>>>

SQL Prompt 使用教程>>>

---

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點(diǎn)擊【咨詢在線客服】