SQL語法提示工具SQL Prompt使用教程：避免在ORDER BY子句中使用常量

---

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

點擊下載SQL Prompt正式版

本文解釋了為什么ORDER BY子句應始終使用其名稱或別名來指定排序列，而不是使用整數來指定列在SELECT列表中的位置。

---

盡管有可能做到這一點，但ANSI SQL-92是最后一個仍支持在ORDER BY子句中使用整數（select_list_number）的標準，隨后又從ANSI SQL-99標準中刪除了該標準。包括SQL Server在內的主要SQL RDBMS仍然支持它，并且沒有棄用通知，但是它在Microsoft即將淘汰的SQL Server功能列表中。

如果您不指定實際的列名，不僅使語句更難以理解，而且對SELECT列表的任何后續(xù)更改（例如更改列順序或添加新列）都需要檢查ORDER BY子句，并且很可能對其進行了修改，以避免產生意外結果。另外，使用常量并不總是有效的，例如使用常量來指定出現在排名函數中的表達式時。

SQL Prompt的最佳實踐代碼分析規(guī)則BP002 –ORDER BY子句中包含常量，這意味著在ORDER BY子句中對常量的任何使用都要加上綠色的下劃線。

為什么在ORDER BY子句中允許使用常量？

想象一下，你需要為AdventureWorks的人力資源經理準備一份員工詳細信息列表：

SELECT Person.FirstName + ' ' + Coalesce(Person.MiddleName + ' ', '')
       + Person.LastName + ': ' + Coalesce(Person.Suffix + ' ', '')
       + Coalesce('(' + Person.Title + ') ', '') + Employee.JobTitle AS employee,
  Employee.NationalIDNumber, Employee.BirthDate, Employee.MaritalStatus,
  Employee.Gender, Employee.HireDate, Employee.SalariedFlag,
  Employee.VacationHours, Employee.SickLeaveHours
  FROM HumanResources.Employee
    INNER JOIN Person.Person
      ON Person.BusinessEntityID = Employee.BusinessEntityID;

沒有指定排序，因此當前排序是隨機的。您現在意識到需要按員工姓名訂購：

SELECT Person.FirstName + ' ' + Coalesce(Person.MiddleName + ' ', '')
       + Person.LastName + ': ' + Coalesce(Person.Suffix + ' ', '')
       + Coalesce('(' + Person.Title + ') ', '') + Employee.JobTitle AS employee,
  Employee.NationalIDNumber, Employee.BirthDate, Employee.MaritalStatus,
  Employee.Gender, Employee.HireDate, Employee.SalariedFlag,
  Employee.VacationHours, Employee.SickLeaveHours
  FROM HumanResources.Employee
    INNER JOIN Person.Person
      ON Person.BusinessEntityID = Employee.BusinessEntityID
  ORDER BY employee;

到處都是微笑。這是有效的。很久以前，有一段時間它不起作用。當時，使用SQL，如果您希望按涉及一列或多列的表達式進行排序，則SQL無法做到這一點。這是因為在執(zhí)行排序時，沒有明顯的方法知道結果中表達式的值。而且，奇怪的是，當時的SQL標準要求您只能在SELECT語句中指定一列作為ORDER BY子句中的參數。

在ORDER BY子句中使用表達式

然后，SQL允許在ORDER BY子句中使用表達式。如果其中一列是表達式，并且您想按其排序，則ORDER BY子句中的表達式必須與SELECT表源語句中使用的表達式完全匹配：

SELECT Person.FirstName + ' ' + Coalesce(Person.MiddleName + ' ', '')
       + Person.LastName + ': ' + Coalesce(Person.Suffix + ' ', '')
       + Coalesce('(' + Person.Title + ') ', '') + Employee.JobTitle AS employee,
  Employee.NationalIDNumber, Employee.BirthDate, Employee.MaritalStatus,
  Employee.Gender, Employee.HireDate, Employee.SalariedFlag,
  Employee.VacationHours, Employee.SickLeaveHours
  FROM HumanResources.Employee
    INNER JOIN Person.Person
      ON Person.BusinessEntityID = Employee.BusinessEntityID
  ORDER BY Person.FirstName + ' ' + Coalesce(Person.MiddleName + ' ', '')
       + Person.LastName + ': ' + Coalesce(Person.Suffix + ' ', '')
       + Coalesce('(' + Person.Title + ') ', '') + Employee.JobTitle;

這不僅繁瑣，而且如果您開始擺弄列表達式并忘記更改ORDER BY以匹配它，則可能突然導致查詢運行緩慢，如果不一致的話：

SELECT Person.FirstName + ' ' + Coalesce(Person.MiddleName + ' ', '')
       + Person.LastName + ': ' + Coalesce(Person.Suffix + ' ', '')
       + Coalesce('[' + Person.Title + '] ', '') + Employee.JobTitle AS employee,
  Employee.NationalIDNumber, Employee.BirthDate, Employee.MaritalStatus,
  Employee.Gender, Employee.HireDate, Employee.SalariedFlag,
  Employee.VacationHours, Employee.SickLeaveHours
  FROM HumanResources.Employee
    INNER JOIN Person.Person
      ON Person.BusinessEntityID = Employee.BusinessEntityID
  ORDER BY Person.FirstName + ' ' + Coalesce(Person.MiddleName + ' ', '')
       + Person.LastName + ': ' + Coalesce(Person.Suffix + ' ', '')
       + Coalesce('(' + Person.Title + ') ', '') + Employee.JobTitle;

由于表達式不匹配，此版本的查詢運行速度降低了25％。沒什么大不了的，卻讓人生氣，看上去很笨拙。

在ORDER BY子句中使用常量

SQL最初通過允許您在ORDER BY表達式中指定一個與要排序的表表達式中的列號相對應的整數常量來解決該問題，而沒有解決潛在的缺陷?，F在可以將SQL Server中的排序列指定為名稱或列別名，或表示該列在選擇列表中位置的正整數。這是不明智的，因為這意味著如果您更改SELECT語句中的列順序，那么事情就會出錯。當ORDER BY表達式出現在排名函數中時，它也不起作用。

SELECT Person.FirstName + ' ' + Coalesce(Person.MiddleName + ' ', '')
       + Person.LastName + ': ' + Coalesce(Person.Suffix + ' ', '')
       + Coalesce('(' + Person.Title + ') ', '') + Employee.JobTitle,
  Employee.NationalIDNumber, Employee.BirthDate, Employee.MaritalStatus,
  Employee.Gender, Employee.HireDate, Employee.SalariedFlag,
  Employee.VacationHours, Employee.SickLeaveHours
  FROM HumanResources.Employee
    INNER JOIN Person.Person
      ON Person.BusinessEntityID = Employee.BusinessEntityID
  ORDER BY 1;

結論

微軟很久以前修復了SQL Server查詢優(yōu)化器中的原始潛在缺陷，即您無法通過其別名引用多列表達式，因為在優(yōu)化過程中此時別名的值未知?，F在這很簡單，但是在此期間，編寫了許多代碼來指定ORDER BY子句中的列號。它使表達式看起來更整潔并節(jié)省了一些擊鍵。他們看起來好像運行得更快，但是共享與完整表達式排序相同的執(zhí)行計劃。在2000年前，從SQL標準中刪除了使用整數常量的快速解決方案，但是其使用壽命很奇怪。建立功能后，很難刪除它。

本文內容到這里就結束了，希望對您有所幫助~您可以繼續(xù)關注我們慧都網，了解更多產品資訊，或者下載SQL Prompt試用版免費體驗~

相關內容推薦：

SQL Prompt 使用教程>>>

---

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊【咨詢在線客服】