如何将我所有选定的列放入虚拟变量中？

背景

这个问题是一个后续问题上一个问题 https://stackoverflow.com/questions/45981422/how-to-measure-query-duration-without-showing-results-of-query。为了在这里也为您提供背景信息，我想总结一下上一个问题：在上一个问题中，我打算有一种方法来执行选择而不将结果发送给客户端。目标是通过发送数百万数据来衡量性能，而不消耗大量资源。我只对执行这些查询所需的时间感兴趣，而不是他们将结果发送到客户端应用程序的时间，因为我打算优化查询，所以查询的结果根本不会改变，但方法会改变改变，我打算能够比较这些方法。

当前知识

在我的另一个问题中提出了一些想法。一个想法是选择记录的计数并将其放入变量中。但是，这显着改变了查询计划，并且结果在性能方面并不准确。也提出了使用临时表的想法，但是如果我们不知道什么查询将作为我们要测量的输入，并且还会引入大量白噪声，那么创建临时表并将其插入其中是很困难的，因此，即使想法很有创意，但对于我的问题来说并不理想。最后，弗拉基米尔·巴拉诺夫提出了一个想法，即创建与选择将返回的列一样多的变量。这是一个好主意，但我通过创建 nvarchar(max) 的单个变量并将所有列选择到其中来进一步完善它。除了一些问题之外，这个想法非常有效。我有大多数问题的解决方案，但我想分享它们，所以，我无论如何都会描述它们，但不要误解我，我有一个问题。

Problem1

如果我有一个@container变量，我做了一个@container = columnname在每个选择内，那么我都会遇到转换问题。

解决方案1

而不是仅仅做一个@container = columnname，我需要做一个@container = cast(columnname as nvarchar(max))

Problem2

我需要转换<whatever> as something into @container = cast(<whatever> as nvarchar(max))对于选择中的每一列，但不适用于子选择，我需要有一个通用的解决方案处理case when和括号，我不想有任何实例@container =任何地方，除了主选择的左侧。

解决方案2

由于我对正则表达式一无所知，因此我可以通过迭代查询字符串直到找到from的主查询，每次我找到括号时，我将不执行任何操作，直到括号关闭为止，找到其中的索引@container =应该放和as [customname]应该取出并从右到左执行查询字符串中的所有操作。这将是一段又长又不优雅的代码。

Question

是否可以确保我的所有主要专栏但没有其他内容以@container =并结束没有as [Customname]?

对于评论来说这太长了，但我想将我的 $.02 添加到其他答案中，并分享我用来测试建议方法的脚本。

我喜欢 @MartinSmith 的 TOP 0 解决方案，但担心在某些情况下它可能会导致不同的执行计划形状。我在运行的测试中没有看到这一点，但我认为您需要验证该计划与您测试的每个查询的不受干扰的查询大致相同。但是，我的测试结果表明列数和/或数据类型可能会影响此方法的性能。

@VladimirBaranov 的答案中的 SQLCLR 方法应该提供应用程序代码生成时的确切计划（假设测试的 SET 选项相同），但 SqlClient 在 SQLCLR 中消耗结果仍然会产生一些轻微的开销 (YMMV)。与将结果返回给调用应用程序相比，使用此方法的服务器开销会更少。

我在第一条评论中建议的 SSMS 丢弃结果方法将比其他方法产生更多开销，但确实包括 SQL Server 不仅在运行查询时执行的服务器端工作，而且还为返回结果填充缓冲区。是否应考虑此额外的 SQL Server 工作取决于测试的目的。对于单元级性能测试，我更喜欢使用与应用程序代码相同的 API 来执行测试。

我通过@MartinSmith 的原始查询使用这 3 种方法捕获了服务器端性能。我的机器上 1,000 次迭代的平均值为：

test method     cpu_time      duration    logical_reads
SSMS discard   53031.000000  55358.844000  7190.512000
TOP 0          52374.000000  52432.936000  7190.527000
SQLCLR         49110.000000  48838.532000  7190.578000

我对返回 10,000 行和 2 列的简单查询执行了相同的操作（int and nvarchar(100)) 来自用户表：

test method     cpu_time      duration    logical_reads
SSMS discard    4204.000000  9245.426000   402.004000
TOP 0           2641.000000  2752.695000   402.008000
SQLCLR          1921.000000  1878.579000   402.000000

重复相同的测试，但使用varchar(100)列而不是nvarchar(100):

test method     cpu_time      duration    logical_reads
SSMS discard    3078.000000  5901.023000   402.004000
TOP 0           2672.000000  2616.359000   402.008000
SQLCLR          1750.000000  1798.098000   402.000000

以下是我用于测试的脚本：

像 @VladimirBaranov 建议的 SQLCLR 过程的源代码：

public static void ExecuteNonQuery(string sql)
{
    using (var connection = new SqlConnection("Context Connection=true"))
    {
        connection.Open();
        var command = new SqlCommand(sql, connection);
        command.ExecuteNonQuery();
    }
}

Xe 跟踪捕获实际的服务器端计时和资源使用情况：

CREATE EVENT SESSION [test] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1))
ADD TARGET package0.event_file(SET filename=N'QueryTimes')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

用户表创建和加载：

CREATE TABLE dbo.Foo(
      FooID int NOT NULL CONSTRAINT PK_Foo PRIMARY KEY
    , Bar1 nvarchar(100)
    , Bar2 varchar(100)
    );
WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Foo WITH (TABLOCKX)
SELECT num, REPLICATE(N'X', 100), REPLICATE('X', 100)
FROM t10k;
GO

从 SSMS 运行 SQL 脚本，并使用丢弃结果查询选项，使用 3 种不同的方法运行 1000 次测试迭代：

SET NOCOUNT ON;
GO

--return and discard results
SELECT v.*,
       o.name
FROM   master..spt_values AS v
       JOIN sys.objects o
         ON o.object_id % NULLIF(v.number, 0) = 0;
GO 1000

--TOP 0
DECLARE @X NVARCHAR(MAX);

SELECT @X = (SELECT TOP 0 v.*,
                          o.name
             FOR XML PATH(''))
FROM   master..spt_values AS v
       JOIN sys.objects o
         ON o.object_id % NULLIF(v.number, 0) = 0;
GO 1000

--SQLCLR ExecuteNonQuery
EXEC dbo.ExecuteNonQuery @sql = N'
SELECT v.*,
       o.name
FROM   master..spt_values AS v
       JOIN sys.objects o
         ON o.object_id % NULLIF(v.number, 0) = 0;
'
GO 1000

--return and discard results
SELECT FooID, Bar1
FROM   dbo.Foo;
GO 1000

--TOP 0
DECLARE @X NVARCHAR(MAX);

SELECT @X = (SELECT TOP 0 FooID, Bar1
             FOR XML PATH(''))
FROM   dbo.Foo;
GO 1000

--SQLCLR ExecuteNonQuery
EXEC dbo.ExecuteNonQuery @sql = N'
SELECT FooID, Bar1
FROM   dbo.Foo
';
GO 1000

--return and discard results
SELECT FooID, Bar1
FROM   dbo.Foo;
GO 1000

--TOP 0
DECLARE @X NVARCHAR(MAX);

SELECT @X = (SELECT TOP 0 FooID, Bar2
             FOR XML PATH(''))
FROM   dbo.Foo;
GO 1000

--SQLCLR ExecuteNonQuery
EXEC dbo.ExecuteNonQuery @sql = N'
SELECT FooID, Bar2
FROM   dbo.Foo
';
GO 1000