http://www.mssqltips.com/tip.asp?tip=1467
大家可以看看这篇文章，讲到的是如何给一个现有表更快速的添加一个自增字段，原始的方法代码：
ALTER TABLE accounts ADD id INT IDENTITY(1,1)
那么作者也贴出来了统计信息：
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 17 ms.
Table 'accounts'. Scan count 1, logical reads 23751, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6281 ms, elapsed time = 48701 ms.
SQL Server Execution Times:
CPU time = 6281 ms, elapsed time = 48474 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
但是最后他寻求到了一个更好的办法：
ALTER TABLE accounts2 ADD id INT
GO
DECLARE @id INT
SET @id = 0
UPDATE accounts2
SET @id = id = @id + 1
GO
统计信息如下：
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 247 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'accounts2'. Scan count 1, logical reads 26384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4781 ms, elapsed time = 4856 ms.
(100000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
呵呵，的确是优化了一些，而且关键这个小技巧应用的还是挺巧，这个技巧在其他地方也有很多的应用。
posted on 2008-05-13 11:13
PerfectDesign 阅读(461)
评论(4) 编辑 收藏 所属分类:
MSSQL调优日志