i have to show running total with the total column in my application ... so i have used the following queries for finding the running total... and i find that both are working as per my need . in one i used the left join with group by and in another one i used the sub query .
现在我的问题是，当我的数据每天增长数千次时，哪一种更快，如果数据限制在 1000 或 2000 行，那么哪一种更好......以及任何其他方法比这两种更快？ ??
declare @tmp table(ind int identity(1,1),col1 int)
insert into @tmp
select 2
union
select 4
union
select 7
union
select 5
union
select 8
union
select 10
SELECT t1.col1,sum( t2.col1)
FROM @tmp AS t1 LEFT JOIN @tmp t2 ON t1.ind>=t2.ind
group by t1.ind,t1.col1
select t1.col1,(select sum(col1) from @tmp as t2 where t2.ind<=t1.ind)
from @tmp as t1
有关在 SQL Server 中计算运行总计的重要资源是这个文件 http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc由 Itzik Ben Gan 提交给 SQL Server 团队，作为其活动的一部分OVER该子句从其最初的 SQL Server 2005 实现进一步扩展。在其中，他展示了一旦进入数万行游标如何执行基于集合的解决方案。 SQL Server 2012 确实扩展了OVER子句使此类查询变得更加容易。
SELECT col1,
SUM(col1) OVER (ORDER BY ind ROWS UNBOUNDED PRECEDING)
FROM @tmp
然而，由于您使用的是 SQL Server 2005，因此您无法使用此功能。
亚当·马卡尼奇在这里显示 http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/running-sums-yet-again-sqlclr-saves-the-day.aspx如何使用 CLR 来提高标准 TSQL 游标的性能。
对于该表定义
CREATE TABLE RunningTotals
(
ind int identity(1,1) primary key,
col1 int
)
我在数据库中创建包含 2,000 行和 10,000 行的表ALLOW_SNAPSHOT_ISOLATION ON一个关闭了此设置（这样做的原因是因为我的初始结果位于数据库中，并且该设置导致了结果的令人费解的方面）。
所有表的聚集索引只有 1 个根页。每个叶子页的数量如下所示。
+-------------------------------+-----------+------------+
| | 2,000 row | 10,000 row |
+-------------------------------+-----------+------------+
| ALLOW_SNAPSHOT_ISOLATION OFF | 5 | 22 |
| ALLOW_SNAPSHOT_ISOLATION ON | 8 | 39 |
+-------------------------------+-----------+------------+
我测试了以下案例（链接显示执行计划）
包含附加 CTE 选项的原因是为了提供一个在以下情况下仍然有效的 CTE 解决方案：ind列不保证顺序。
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @col1 int, @sumcol1 bigint;
WITH RecursiveCTE
AS (
SELECT TOP 1 ind, col1, CAST(col1 AS BIGINT) AS Total
FROM RunningTotals
ORDER BY ind
UNION ALL
SELECT R.ind, R.col1, R.Total
FROM (
SELECT T.*,
T.col1 + Total AS Total,
rn = ROW_NUMBER() OVER (ORDER BY T.ind)
FROM RunningTotals T
JOIN RecursiveCTE R
ON R.ind < T.ind
) R
WHERE R.rn = 1
)
SELECT @col1 =col1, @sumcol1=Total
FROM RecursiveCTE
OPTION (MAXRECURSION 0);
所有的查询都有一个CAST(col1 AS BIGINT)添加以避免运行时溢出错误。此外，对于所有这些，我将结果分配给上述变量，以消除发送回结果所花费的时间。
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| | | | Base Table | Work Table | Time |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| | Snapshot | Rows | Scan count | logical reads | Scan count | logical reads | cpu | elapsed |
| Group By | On | 2,000 | 2001 | 12709 | | | 1469 | 1250 |
| | On | 10,000 | 10001 | 216678 | | | 30906 | 30963 |
| | Off | 2,000 | 2001 | 9251 | | | 1140 | 1160 |
| | Off | 10,000 | 10001 | 130089 | | | 29906 | 28306 |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| Sub Query | On | 2,000 | 2001 | 12709 | | | 844 | 823 |
| | On | 10,000 | 2 | 82 | 10000 | 165025 | 24672 | 24535 |
| | Off | 2,000 | 2001 | 9251 | | | 766 | 999 |
| | Off | 10,000 | 2 | 48 | 10000 | 165025 | 25188 | 23880 |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| CTE No Gaps | On | 2,000 | 0 | 4002 | 2 | 12001 | 78 | 101 |
| | On | 10,000 | 0 | 20002 | 2 | 60001 | 344 | 342 |
| | Off | 2,000 | 0 | 4002 | 2 | 12001 | 62 | 253 |
| | Off | 10,000 | 0 | 20002 | 2 | 60001 | 281 | 326 |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| CTE Alllows Gaps | On | 2,000 | 2001 | 4009 | 2 | 12001 | 47 | 75 |
| | On | 10,000 | 10001 | 20040 | 2 | 60001 | 312 | 413 |
| | Off | 2,000 | 2001 | 4006 | 2 | 12001 | 94 | 90 |
| | Off | 10,000 | 10001 | 20023 | 2 | 60001 | 313 | 349 |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
相关子查询和GROUP BY版本使用由聚集索引扫描驱动的“三角形”嵌套循环连接RunningTotals table (T1) 并且，对于该扫描返回的每一行，重新查找表 (T2) 自加入T2.ind<=T1.ind.
这意味着相同的行会被重复处理。当。。。的时候T1.ind=1000行被处理，自连接检索所有行并将其求和ind <= 1000，然后对于下一行T1.ind=1001检索到相同的 1000 行again并与另外一行一起求和，依此类推。
对于 2,000 行的表，此类操作的总数为 2,001,000，对于 10k 行，此类操作的总数为 50,005,000 或更多(n² + n) / 2 http://mathworld.wolfram.com/ArithmeticSeries.html显然呈指数增长。
在 2,000 行的情况下，GROUP BY子查询版本是前者在连接后具有流聚合，因此有三列输入其中（T1.ind, T2.col1, T2.col1) and a GROUP BY的财产T1.ind而后者被计算为标量聚合，连接之前的流聚合仅具有T2.col1喂进去并且没有GROUP BY属性完全设置。可以看出，这种更简单的安排在减少 CPU 时间方面具有显着的好处。
对于 10,000 行的情况，子查询计划还有一个额外的差异。它添加了一个热切的线轴 http://technet.microsoft.com/en-us/library/ms190435.aspx它复制了所有的ind,cast(col1 as bigint)值转化为tempdb。在启用快照隔离的情况下，这比聚集索引结构更紧凑，最终效果是将读取次数减少约 25%（因为基表为版本控制信息保留了相当多的空白空间），当此选项关闭时，它的结构会不太紧凑（大概是由于bigint vs int差异）和更多的读取结果。这减少了子查询和 group by 版本之间的差距，但子查询仍然获胜。
然而，明显的赢家是递归 CTE。对于“无间隙”版本，现在从基表进行逻辑读取2 x (n + 1)反映了nindex 查找 2 级索引以检索所有行以及末尾的附加行，该行不返回任何内容并终止递归。然而，这仍然意味着要处理 22 页表需要 20,002 次读取！
递归 CTE 版本的逻辑工作表读取非常高。似乎每个源行有 6 次工作表读取。这些来自存储前一行输出的索引假脱机，然后在下一次迭代中再次读取（Umachandar Jayachandran 对此做了很好的解释）here http://www.eggheadcafe.com/software/aspnet/30627951/spools.aspx）。尽管数量很高，但这仍然是表现最好的。