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

• a lot more work to set up (as in 10x - 20x more): you need a real table to represent each distinct result set, you need a trigger, the trigger needs to either be built dynamically, or get the number of rows to return from some config table, or I suppose it could get it from CONTEXT_INFO() or a temp table. Still, the whole process is quite a few steps and convoluted.

非常效率低下:首先，它做了相同数量的工作，将完整的结果集转储到一个表中(即到 INSERTED 表中——它位于[tempdb]) 但随后它执行了一个额外的步骤来选择所需的记录子集(这不是真正的问题，因为这应该仍然在缓冲池中)以返回到真正的表中.更糟糕的是，第二步实际上是双 I/O，因为操作也在该真实表所在的数据库的事务日志中表示.但是等等，还有更多:查询的下一次运行怎么样?你需要清除这张真实的桌子.无论是通过DELETE还是TRUNCATE TABLE，它都是在事务日志中显示的另一个操作(基于使用这两个操作中的哪一个的表示量)，加上是额外操作花费的额外时间.并且，我们不要忘记从 INSERTED 中选择子集到真实表中的步骤:它没有机会使用索引，因为您无法索引 INSERTED 和 DELETED 表.并不是说您总是想向临时表添加索引，但有时它会有所帮助(取决于具体情况)并且您至少可以选择.

very inefficient: first it does the same amount of work dumping the full result set to a table (i.e. into the INSERTED table--which lives in [tempdb]) but then it does an additional step of selecting the desired subset of records (not really a problem as this should still be in the buffer pool) to go back into the real table. What's worse is that second step is actually double I/O as the operation is also represented in the transaction log for the database where that real table exists. But wait, there's more: what about the next run of the query? You need to clear out this real table. Whether via DELETE or TRUNCATE TABLE, it is another operation that shows up (the amount of representation based on which of those two operations is used) in the transaction log, plus is additional time spent on the additional operation. AND, let's not forget about the step that selects the subset out of INSERTED into the real table: it doesn't have the opportunity to use an index since you can't index the INSERTED and DELETED tables. Not that you always would want to add an index to the temp table, but sometimes it helps (depending on the situation) and you at least have that choice.

过于复杂:当两个进程需要同时运行查询时会发生什么?如果它们共享相同的真实表以转储到最终输出中，然后从中选择，则需要添加另一列以区分 SPID.它可以是 @@SPID.或者它可以是在调用初始 INSERT 到实际表之前创建的 GUID(以便它可以通过 CONTEXT_INFO() 传递给 INSTEAD OF 触发器 或临时表).无论值是多少，一旦选择了最终输出，它将用于执行 DELETE 操作.如果不明显，这部分会影响之前项目符号中提出的性能问题:TRUNCATE TABLE 不能使用，因为它会清除整个表，留下 DELETE FROM dbo.RealTable WHERE ProcessID = @WhatID; 作为唯一的选项.

overly complicated: what happens when two processes need to run the query at the same time? If they are sharing the same real table to dump into and then select out of for the final output, then there needs to be another column added to distinguish between the SPIDs. It could be @@SPID. Or it could be a GUID created before the initial INSERT into the real table is called (so that it can be passed to the INSTEAD OF trigger via CONTEXT_INFO() or a temp table). Whatever the value is, it would then be used to do the DELETE operation once the final output has been selected. And if not obvious, this part influences a performance issue brought up in the prior bullet: TRUNCATE TABLE cannot be used as it clears the entire table, leaving DELETE FROM dbo.RealTable WHERE ProcessID = @WhateverID; as the only option.

现在，公平地说，可能从触发器本身内部执行最终的 SELECT.这将减少一些低效率，因为数据永远不会进入真正的表，然后也永远不需要删除.它还减少了过度复杂化，因为不需要通过 SPID 分离数据.然而，这是一个非常有时间限制的解决方案，因为在 SQL Server 的下一个版本中，从触发器中返回结果的能力将被淘汰，禁止来自触发器服务器配置选项的结果:

Now, to be fair, it is possible to do the final SELECT from within the trigger itself. This would reduce some of the inefficiency as the data never makes it into the real table and then also never needs to be deleted. It also reduces the over-complication as there should be no need to separate the data by SPID. However, this is a very time-limited solution as the ability to return results from within a trigger is going bye-bye in the next release of SQL Server, so sayeth the MSDN page for the disallow results from triggers Server Configuration Option:

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

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you set this value to 1.

唯一可行的方法:

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

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

is to use .Net. If the procs are being called from app code, please see "EDIT 2" at the bottom. If you want to be able to randomly run various stored procedures via ad hoc queries, then it would have to be a SQLCLR stored procedure so that it could be generic and work for any query as stored procedures can return dynamic result sets and functions cannot. The proc would need at least 3 parameters:

• @QueryToExec NVARCHAR(MAX)
• @RowsToReturn INT
• @TotalRows INT 输出

想法是使用Context Connection = true;"利用内部/进程内连接.然后执行以下基本步骤:

The idea is to use "Context Connection = true;" to make use of the internal / in-process connection. You then do these basic steps:

1. 调用ExecuteDataReader()
2. 在读取任何行之前，先执行一个 GetSchemaTable()
3. 从 SchemaTable 获得结果集字段名称和数据类型
4. 从结果集结构中构造一个 SqlDataRecord
5. 用那个 SqlDataRecord 你调用 SqlContext.Pipe.SendResultsStart(_DataRecord)
6. 现在你开始调用 Reader.Read()
7. 对于您调用的每一行:

1. call ExecuteDataReader()
2. before you read any rows, do a GetSchemaTable()
3. from the SchemaTable you get the result set field names and datatypes
4. from the result set structure you construct a SqlDataRecord
5. with that SqlDataRecord you call SqlContext.Pipe.SendResultsStart(_DataRecord)
6. now you start calling Reader.Read()
7. for each row you call:

1. Reader.GetValues()
2. DataRecord.SetValues()
3. SqlContext.Pipe.SendResultRow(_DataRecord)
4. RowCounter++

不确定这对临时表方法、双重调用方法，甚至@M.Ali 的方法(我也尝试过并且有点像，但问题特定于not将值作为列发送)，但它应该没问题并且确实按要求完成了任务.

Not sure how this performs against the temp table method, the dual call method, or even @M.Ali's method (which I have also tried and kinda like, but the question was specific to not sending the value as a column), but it should be fine and does accomplish the task as requested.

甚至更好！另一种选择(上述 C# 建议的变体)是使用 T-SQL 存储过程中的 @@ROWCOUNT，作为 OUTPUT 参数发送，而不是循环SqlDataReader 中的其余行.所以存储过程类似于:

Even better! Another option (a variation on the above C# suggestion) is to use the @@ROWCOUNT from the T-SQL stored procedure, sent as an OUTPUT parameter, rather than cycling through the rest of the rows in the SqlDataReader. So the stored procedure would be similar to:

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)更改为:

Then, in the app code, create a new SqlParameter, Direction = Output, for "@RowCount". The numbered steps above stay the same, except the last two (10 and 11), which change to:

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

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

I have tried this and it does work. But so far I have not had time to test the performance against the other methods.

编辑 2:
如果从应用层调用 T-SQL 存储过程(即不需要临时执行)，那么这实际上是上述 C# 方法的更简单的变体.在这种情况下，您无需担心 SqlDataRecord 或 SqlContext.Pipe 方法.假设您已经设置了一个 SqlDataReader 来拉回结果，您只需要:

EDIT 2:
If the T-SQL stored procs are being called from the app layer (i.e. no need for ad hoc execution) then this is actually a much simpler variation of the above C# methods. In this case you don't need to worry about the SqlDataRecord or the SqlContext.Pipe methods. Assuming you already have a SqlDataReader set up to pull back the results, you just need to:

1. 确保 T-SQL 存储过程具有 @RowCount INT OUTPUT = -1 参数
2. 确保在查询后立即SET @RowCount = @@ROWCOUNT;
3. 将 OUTPUT 参数注册为具有方向 = 输出的 SqlParameter
4. 使用类似于:while(Reader.Read() && RowCounter < RowsToReturn) 的循环，这样您就可以在撤回所需数量后停止检索结果.立>
5. 记住不要限制存储过程中的结果(即没有TOP (n))

1. Make sure the T-SQL stored proc has a @RowCount INT OUTPUT = -1 parameter
2. Make sure to SET @RowCount = @@ROWCOUNT; immediately after the query
3. Register the OUTPUT param as a SqlParameter having Direction = Output
4. Use a loop similar to: while(Reader.Read() && RowCounter < RowsToReturn) so that you can stop retrieving results once you have pulled back the desired amount.
5. Remember to not limit the result in the stored proc (i.e. no TOP (n))

此时，就像上面第一个编辑"中提到的那样，只需关闭 SqlDataReader 并获取 OUTPUT 参数的 .Value :).

At that point, just like what was mentioned in the first "EDIT" above, just close the SqlDataReader and grab the .Value of the OUTPUT param :).

这篇关于TSQL:有没有办法限制返回的行数并计算没有限制返回的总数(不将其添加到每一行)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！