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.
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
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.
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.
呵呵,的确是优化了一些,而且关键这个小技巧应用的还是挺巧,这个技巧在其他地方也有很多的应用。