logo SQL Prompt教程 我也要发布文档

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)。

SQL????¤o????£??μ???°??£? ??°???3PE020-?????¨ORDER BY?????¥??°?°??1?è?¨??-?????¥èˉ¢???

这样做是为了找出容易犯的错误,即留下不再需要的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正版授权,或了解更多产品信息请点击【咨询在线客服】