sql - TSQL:是否有办法限制返回的行并计算没有限制的返回总数(不将其添加到每一行)？

Question

我正在更新一个存储过程，该存储过程当前最多选择n行，如果返回的行= n，则执行无限制的选择计数，然后返回原始的select和受影响的行总数。

有点像：

SELECT TOP (@rowsToReturn)
    A.data1,
    A.data2
FROM
    mytable A

SET @maxRows = @@ROWCOUNT
IF @rowsToReturn = @@ROWCOUNT
BEGIN
  SET @maxRows = (SELECT COUNT(1) FROM mytableA)
END    

我想将其简化为单个select语句。基于 this question， COUNT(*) OVER()允许这样做，但是它放在每一行中，而不是放在输出参数中。也许像MYSQL中的 FOUND_ROWS()之类的东西，例如@@ TOTALROWCOUNT之类。

附带说明一下，由于实际选择的顺序是一个顺序，因此数据库将需要遍历整个集合（以确保它获得了正确的前n个有序记录），因此数据库应该已经在某处进行了计数。

Answer 1

正如@MartinSmith在对此问题的评论中提到的那样，没有直接（即纯T-SQL）方法来获取将要返回的行总数，同时限制了它。过去，我已完成以下方法：


将查询转储到临时表中以获取@@ROWCOUNT（总集）
在主查询的排序结果上使用ROW_NUBMER() AS [ResultID]
SELECT TOP (n) FROM #Temp ORDER BY [ResultID]或类似的内容


当然，这里的缺点是您需要将这些记录放入temp表中的磁盘I / O成本。将[tempdb]放在SSD上？ :)



我还经历了“先用其余的查询运行COUNT（*），然后再运行常规的SELECT”方法（如@Blam所倡导的那样），这不是对查询的“免费”重新运行：


在许多情况下，它是完全重新运行。问题是，在执行COUNT(*)时（因此不返回任何字段），优化器仅需要担心JOIN，WHERE，GROUP BY，ORDER BY子句中的索引。但是，当您需要返回一些实际数据时，这可能会相当大地改变执行计划，尤其是如果用于获取COUNT（*）的索引没有“覆盖” SELECT列表中的字段时。
另一个问题是，即使索引全部相同，因此所有数据页仍在高速缓存中，这只会使您免于物理读取。但是您仍然具有逻辑读物。


我并不是说这种方法行不通，但我认为Question中仅在条件上仅COUNT(*)起作用的方法对系统的压力要小得多。



@Gordon提倡的方法实际上在功能上与我上面描述的临时表方法非常相似：它将全部结果集转储到[tempdb]（INSERTED表在[tempdb]中）以获得完整的@@ROWCOUNT和然后得到一个子集。不利的一面是，INSTEAD OF TRIGGER方法是：


需要做更多的工作（例如，增加10倍至20倍）：您需要一个真实的表来表示每个不同的结果集，需要一个触发器，该触发器需要动态构建，或者获取要返回的行数从某些配置表，或者我想它可以从CONTEXT_INFO()或临时表中获取它。尽管如此，整个过程还是很多步骤，而且很复杂。
非常低效：首先，它执行相同的工作量，将全部结果集转储到表中（即，放入INSERTED表中，该表位于[tempdb]中），然后又执行了另外一个选择所需记录子集的步骤（这不是真正的问题，因为它仍应位于缓冲池中）以返回到实际表中。更糟糕的是，第二步实际上是双重I / O，因为该操作还在实际表所在的数据库的事务日志中表示。但是，等等，还有更多：下一次查询运行如何？您需要清除此真实表。不管是通过DELETE还是TRUNCATE TABLE，它都是在事务日志中显示的另一项操作（基于使用这两个操作中的哪一个来表示的数量），加上在该附加操作上花费的额外时间。 AND，别忘了从INSERTED中选择子集进入真实表的步骤：由于您无法为INSERTED和DELETED表建立索引，因此它没有机会使用索引。并不是说您总是想向临时表添加索引，但是有时它会有所帮助（取决于情况），并且您至少可以选择。
过于复杂：当两个进程需要同时运行查询时会发生什么？如果它们共享同一真实表以转储到然后选择out作为最终输出，则需要添加另一列以区分SPID。可能是@@SPID。也可以是在调用初始INSERT到真实表之前创建的GUID（以便可以通过INSTEAD OF或临时表将其传递给CONTEXT_INFO()触发器）。无论值是多少，一旦选择了最终输出，它将用于执行DELETE操作。而且，即使不是很明显，这部分也会影响前面的项目符号中提到的性能问题：TRUNCATE TABLE不能使用，因为它会清除整个表，而DELETE FROM dbo.RealTable WHERE ProcessID = @WhateverID;则是唯一的选择。

现在，公平地说，可以从触发器本身内部进行最终的SELECT。这将减少一些效率低下的情况，因为数据永远不会进入真实表，然后再也不需要删除。由于也无需通过SPID分离数据，因此也减少了过度复杂的情况。但是，这是一个非常有限的解决方案，因为在SQL Server的下一发行版中，从触发器内返回结果的能力正在逐渐消失，因此请说一下disallow results from triggers Server Configuration Option的MSDN页面：


在下一版本的Microsoft SQL Server中将删除此功能。不要在新的开发工作中使用此功能，请尽快修改当前使用此功能的应用程序。我们建议您将此值设置为1。





唯一的实际方法是：


查询一次
获取行的子集
仍然获得完整结果集的总行数


是使用.Net。如果从应用代码中调用了proc，请参阅底部的“ EDIT 2”。如果您希望能够通过即席查询随机运行各种存储过程，则它必须是SQLCLR存储过程，以便它可以是通用的并且适用于任何查询，因为存储过程可以返回动态结果集而函数不能。该过程至少需要3个参数：


@QueryToExec NVARCHAR（最大）
@RowsToReturn INT
@TotalRows INT输出


这个想法是使用“ Context Connection = true;”。利用内部/进程内连接。然后，您执行以下基本步骤：


致电ExecuteDataReader()
在读取任何行之前，请先执行GetSchemaTable()
从SchemaTable中，您可以获得结果集字段名称和数据类型
从结果集结构中构造一个SqlDataRecord
用那个SqlDataRecord您呼叫SqlContext.Pipe.SendResultsStart(_DataRecord)
现在您开始呼叫Reader.Read()
对于您调用的每一行：

Reader.GetValues()
DataRecord.SetValues()
SqlContext.Pipe.SendResultRow(_DataRecord)
RowCounter++

而不是执行典型的“ while (Reader.Read())”，而是包括@RowsToReturn参数：while(Reader.Read() && RowCounter < RowsToReturn.Value)
在while循环之后，调用SqlContext.Pipe.SendResultsEnd()关闭结果集（正在发送的结果集，而不是正在读取的结果集）
然后执行第二次while循环，循环遍历其余结果，但从不获取任何字段：
而（Reader.Read（））
{
RowCounter ++;
}
然后只需设置TotalRows = RowCounter;即可返回完整结果集的行数，即使您只返回了它的前n行：）


不知道它是如何针对临时表方法，双重调用方法甚至@ M.Ali的方法（我也曾尝试过，有点类似，但是问题是不将值作为列发送），但是它应该很好，并且可以按要求完成任务。

编辑：
更好！另一个选择（上述C＃建议的一种变化）是使用T-SQL存储过程中的@@ROWCOUNT作为OUTPUT参数发送，而不是循环遍历SqlDataReader中的其余行。因此，存储过程将类似于：

CREATE PROCEDURE SchemaName.ProcName
(
   @Param1 INT,
   @Param2 VARCHAR(05),
   @RowCount INT OUTPUT = -1 -- default so it doesn't have to be passed in
)
AS
SET NOCOUNT ON;

{any ol' query}

SET @RowCount = @@ROWCOUNT;

然后，在应用程序代码中，为“ @RowCount”创建一个新的SqlParameter，Direction = Output。上面编号的步骤保持不变，除了最后两个步骤（10和11）更改为：


而不是第二个while循环，只需调用 Reader.Close()
代替使用RowCounter变量，而是设置 TotalRows = (int)RowCountOutputParam.Value;


我已经尝试过了，它确实有效。但是到目前为止，我还没有时间对照其他方法测试性能。

编辑2：
如果从应用程序层调用T-SQL存储的proc（即无需临时执行），则实际上这是上述C＃方法的简单得多的变体。在这种情况下，您不必担心 SqlDataRecord或 SqlContext.Pipe方法。假设您已经设置了 SqlDataReader来拉回结果，则只需要：


确保T-SQL存储的proc具有@RowCount INT OUTPUT = -1参数
确保查询后立即 SET @RowCount = @@ROWCOUNT;
将OUTPUT参数注册为具有方向=输出的 SqlParameter
使用类似于以下内容的循环： while(Reader.Read() && RowCounter < RowsToReturn)，以便在您拉回所需的数量后就可以停止检索结果。
切记不要在存储过程中限制结果（即否 TOP (n)）


那时，就像上面的第一个“ EDIT”中提到的一样，只需关闭 SqlDataReader并获取OUTPUT参数的 .Value即可：)。