SQL语法提示工具SQL Prompt教程：为什么SELECT *（BP005）在生产代码中不好？（上）

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

点击下载SQL Prompt试用版

如果“提示”警告您在SELECT语句中使用星号或“star”（*），请考虑将其替换为显式列列表。它将防止不必要的网络负载和查询性能问题，并避免在插入表时如果列顺序更改而造成问题。本文是该教程的上半部分内容~

关于SELECT *，我应该说第一件事是我们所有人都在使用它。我每天都将其用于开发工作，但除非有充分的理由，否则很少会投入生产。在开发之外，SELECT *在一个批处理中只有几个有效的用途。可以使用SELECT * FROM内部IF EXISTS语句SELECT COUNT(*)，SELECT * FROM…FOR XML或者也可以SELECT * FROM…FOR JSON。

在其他地方，使用星号作为列说明符的查询可以在一段时间内正常工作，但会带来漏洞和性能问题。如果假设列顺序是“固定的”，通过使用INSERT INTO… SELECT * FROM语句传输数据，那么当该顺序更改时，您最大的希望是它会导致错误，但在最坏的情况下，您会看到分配错误数据的可怕后果。同样，选择表中所有列的查询（即使应用程序不需要全部）也会导致不必要的网络负载和查询性能问题。这也使查询很难索引，从而导致执行计划效率低下。

最后，SELECT *这还将使您的代码可读性降低，并且难以维护。所有这些都解释了为什么SQL Prompt实现“最佳实践”代码分析规则（BP005），该规则将提示您*在SELECT语句中用明确的列列表替换asterisk（）。

使用SELECT *进行开发工作

SELECT *是临时工作的理想选择，可以在开始优化查询或调试时准确地查看结果，但它是一种旨在交互式使用的开发设备。如果没有SELECT *，SQL Server的开发工作会更痛苦，因为查不到关于正在从表中源返回的数据和元数据，这意味着任何可以进入from子句的任何内容。这将包括基本表、视图、TABLESAMPLE、派生表、连接表和表函数。

简而言之，SELECT *是观察数据必不可少的。当然，sp_help它将为您提供有关表和其他对象的信息，并且您拥有SSMS浏览器以及希望的SQL Prompt，但是在调查查询、表值函数或过程的结果时，看到该SSMS数据网格无所不能。

从表、视图或TVF获取列名列表仍然是一种流行的方法。但是，如果您只需要一个表或视图的列列表（而不是TVF），则可以将表的Columns子文件夹从SSMS对象资源管理器拖到查询窗格中。或者，在这种情况下，在目标数据库的上下文中Adventureworks2016，您可以使用以下方法调查表：

SELECT col.name
  FROM sys.objects obj -- from all the objects (system and database)
      INNER JOIN sys.columns col --to get all the columns
          ON col.object_id = obj.object_id
  WHERE obj.object_id = OBJECT_ID('[Person].[Address]')
  ORDER BY col.column_id;

SELECT *的优点是您可以复制和粘贴任何结果的列名，而不管其来源如何。但是，如今最好将sys.dm_exec_describe_first_result_set用于语句，将sp_describe_first_result_set用于批处理。例如，如果我们想查看Person.Address表中的哪些列AdventureWorks，可以使用：

SELECT f.name
  FROM sys.dm_exec_describe_first_result_set
   (N'SELECT * FROM adventureworks2016.Person.Address;', NULL, 0) AS f; 
         -- (@tsql, @Params, @include_browse_information)

为什么SELECT *在生产代码中不好？

一旦您超出了调查阶段，SELECT *应将其替换为显式的列列表，否则可能导致问题：

• 消化不良：数据库和应用程序中，您请求的每一列数据都需要付费。当您想要一杯水时，您不必打开房子的每一个水龙头。当在应用程序中指定所有列而不是仅指定所需的列时，您最初将知道，甚至可以接受浪费的程度。如果随后有人使用更多列扩展了表的宽度，则您会得到很多不必要的列，并且由于增加的内存管理任务，您的应用程序将变慢。

• 麻木。检索信息的查询可能会使用索引，特别是如果您的查询使用过滤器。SQL Server的查询优化器将尽可能从覆盖索引中获取数据，而不必在聚集索引中四处逛逛。如果使用SELECT *，则很有可能没有索引被覆盖。即使您足够笨拙地使用非聚集索引覆盖整个表，但是如果表的大小进一步增加，则将完全变得毫无用处。

• 误解：如果您按顺序而不是名称来引用列，使用INSERT INTO… SELECT *，那么您必须希望列的顺序永远不会改变。如果是这样，并且有可能将值强制转换为您期望的数据类型，那么数据可能会以错误的列结尾，而不会触发错误。

• 约束问题。当您使用星号显示的查询SELECT…INTO时，很容易遇到列名重复的问题。如果指定列，则将在重复项之前知道它们并可以对其进行别名。相反，如果将这样的查询传递给应用程序，则无法轻松地知道哪一列是正确的值。

• 可维护性：任何阅读您的代码并看到的SELECT *人都必须搜索元数据，以优化查询所引用的列的名称。如果您列出它们，并且它们是有意义的名称，那么将会更清楚发生了什么，并且查询的目的将更加明显。

消化不良

在应用程序中，我见过的最坏习惯可能是指定不需要的数据。在一些开发人员中间，人们误解了数据库速度很慢，但是每个查询花费的时间大约相同。通过这种错误的逻辑，在一个查询中获取所有内容就很有意义，并且SELECT *意味着：“把所拥有的全部给我”。就像一个销售购物者，他不得不排队，并且从货架上抢走了他们可能无法承受或负担得起的更多廉价商品。

具有讽刺意味的是，查询中许多明显的缓慢结果并不是“数据库正在缓慢”，而是在不必要的数据通过网络拉动而不得不将其压缩在大型对象中的情况下，本地内存管理陷入困境。如果要从数据库将数据加载到对象中，通常最好使用延迟加载，尤其是在对象较大的情况下。就花费的时间而言，跨网络传递数据的开销令人惊讶地昂贵，因此最好只请求查询中立即需要的内容。几个快速查询比一个庞大的超级查询要快。

即使在SQL Server中，SELECT *也会咬你。它突破了一般性原则，出于性能原因，您在过滤和投影数据时会尽快降低结果。这意味着您在进行诸如聚合之类的高级操作之前，首先将列和行都缩减为所需的内容。现在，在查询中的派生表中使用SELECT *对性能的危害要小于以前，这是因为优化器现在通常可以将列限制在必要的范围内，但这仍然是错误的，尤其是在外部查询中。 结果应始终仅包含所需的列。

为了演示其影响，在这里我通过使用时序表中的SELECT…INTO在时序工具中创建两个临时表，并用Directory表中的200万行数据填充它们。在其中一个例子中，我只指定了我需要的列，在另一个中，我耸了耸肩，然后键入*。

--set up
  DECLARE @log TABLE (TheOrder INT IDENTITY(1,1), 
    WhatHappened varchar(200), 
    WhenItDid  Datetime2 DEFAULT GETDATE())
  ----start of timing
  INSERT INTO @log(WhatHappened) SELECT 'Starting to time select into'--place at the start
  --end of setup
  SELECT * INTO #myTempDirectory FROM Directory;
  INSERT INTO @log (WhatHappened) 
    SELECT 'SELECT INTO with wildcard took';
     --copy and paste in anywhere
  SELECT Name, Address1, Address2, Town, City, County, Postcode, Region
    INTO #AnotherTempDirectory
    FROM Directory;
  INSERT INTO @log (WhatHappened) 
    SELECT 'SELECT INTO with fields specified took ';
     --where the routine you want to time ends
  SELECT ending.WhatHappened,
    DateDiff(ms, starting.WhenItDid, ending.WhenItDid) AS ms
    FROM @log AS starting
      INNER JOIN @log AS ending
        ON ending.TheOrder = starting.TheOrder + 1
  UNION ALL
  SELECT 'Total', DateDiff(ms, Min(WhenItDid), Max(WhenItDid)) FROM @log;
  --list out all the timings
  --tear down
  DROP TABLE #myTempDirectory;
  DROP TABLE #AnotherTempDirectory;

尽管我们仅将数据量减少了30％，但指定列的版本只花费了另一列的20％的时间。

换句话说，对于本测试，使用通配符SELECT…INTO的时间是仅返回所需列的SELECT…INTO的五倍。

麻木

几乎不可能为SELECT *查询提供覆盖索引，即使您要尝试，也要比仅简单地明确拼出所需的列要困难得多。

如果您只是在获取信息，SQL Server通常将使用索引。对于频繁、重要且昂贵的查询，您可能希望它具有一个可用的索引，它可以从中检索查询请求的所有列。如果没有，则可以从索引中获取可用列，然后在聚簇索引中查找其他列的值，或者可以简单地扫描聚簇索引。如果表很大并且查询返回很多行，您将开始注意到性能下降。

假设我们要搜索Directory表格以查找给定城市中所有被称为“某物”的酒吧。我们可以做到

SELECT Name, Address1, Address2, Town, City, County, Postcode, Region FROM Directory WHERE name LIKE '% arms%' and city ='Cambridge'

或者我们可以这样做...

SELECT * FROM Directory WHERE name LIKE '% arms%' and city ='Cambridge'

第一个查询可能具有如下覆盖索引：

CREATE NONCLUSTERED INDEX IX_Directory_City
  ON [dbo].[Directory] ([City])
  INCLUDE ([Address1], [Address2], [Town], [County], [Postcode], [Region])
  GO

但是，如果查询为SELECT *，那么合理的索引策略将无济于事。第二个版本只能包含每个列的索引！

使用与以前相同的测试工具，即使大多数工作都是在剑桥所有企业的名称中搜索“武器”一词，您也可以更快地指定列。在第一种情况下，索引是此查询的覆盖索引，而对于SELECT *版本，则不是。执行计划仍使用它，因为在这种情况下查询仅返回19行，但是SQL Server必须为每行执行其他键查找，以返回此索引中未包含的列值。

在此数据库上进行更一般的查询后，指定列的查询平均使用了相同索引但需要进行额外的键查找的时间的三分之二。

本教程尚未完结，后面会接着更新本教程后半部分的内容，大家敬请期待哦~您可以关注我们了解更多该产品信息，或者下载SQL Prompt免费版进行测试~

相关内容推荐：

SQL Prompt系列教程>>>

想要购买SQL Prompt正版授权，或了解更多产品信息请点击