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正版授权，或了解更多产品信息请点击