NOCOUNT如何影響ADO.NET（SET NOCOUNT ON的效能問題）

How NOCOUNT affects ADO.NET一文中提到：

Previously, in Classic ASP and ADO, NOCOUNT was most commonly a factor if you were checking RecordsAffected on Command.Execute(). SqlDataReader does have a RecordsAffected property, but it's rarely used since it's not populated until the datareader has iterated all the rows and the datareader is closed. There are some possible implications if you're using DataAdapters to submit changes to the database, since it uses the rows affected result to determine if the update succeeded. Probably the easiest way to check for that case is to search the codebase for SqlDataAdapter and see if the Update() method is being called.

還有：

Tech notes if you're interested in tracing how RecordsAffected is set and used by pointing Reflector at System.Data:
System.Data.SqlClient.TdsParser.ProcessDone() sets RecordsAffected.
System.Data.Common.DbDataAdapter.UpdateRow() uses reader.RecordsAffected in determining whether to call AcceptChanges and ApplyToDataRow.

保險一點來說，通過作者所說的調用Update()來做判斷似乎並不可靠，中間有很多步驟都有可能出問題從而影響最終的結果。同時，debug起來的也有麻煩了，嵌入sql debug後對於偵錯工具很有用，而如果按作者所說這個debug的功能都被削減了。又把database和application給分開調了。

評論中有人寫了一個簡單的測試程式，並給出了他的測試結果，他認為還是SET NOCOUNT ON快。

測試使用的預存程序
DECLARE @Start DATETIME, @End DATETIME, @Counter INT, @NoCountOff INT, @NoCountOn INT

CREATE TABLE #MyTable (

Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY

, FirstName VARCHAR(128) NOT NULL

, LastName VARCHAR(128) NOT NULL

)

SET NOCOUNT OFF

SET @Counter = 1

SET @Start = GETDATE()

WHILE @Counter <= 150000

BEGIN

INSERT #MyTable (FirstName, LastName) VALUES ('Scott', 'Whigham')

SET @Counter = @Counter + 1

END

SET @End = GETDATE()

SELECT @NoCountOff = DATEDIFF(ms, @Start, @End)

SET NOCOUNT ON

SET @Counter = 1

SET @Start = GETDATE()

WHILE @Counter <= 150000

BEGIN

INSERT #MyTable (FirstName, LastName) VALUES ('Scott', 'Whigham')

SET @Counter = @Counter + 1

END

SET @End = GETDATE()

SELECT @NoCountOn = DATEDIFF(ms, @Start, @End)

SELECT @NoCountOff AS 'NoCountOff', @NoCountOn AS 'NoCountOn', COUNT(*) AS NumberOfRows FROM #MyTable

DROP TABLE #MyTable

我與他測試的環境不同，在xp + SQL 2005 Ent with SP1上測試的，結果結論相反，把結果資料也share給大家：

NoCountOff   |   NoCountOn | xRate

7106    5893    20.58%
6156    6890    -10.61%
7093    7343    -3.41%
5780    6406    -9.77%
5563    6860    -18.91%
5610    7390    -24.09%
5810    6923    -21.38%
6296    7436    -15.33%

-- insert 1
46    0
16    0
0    0
0    0
16    0

-- insert 1000
420     393     6.88%
423     390     8.47%
966     423     128.37%

-- insert 100,000
43250    42860    0.91%

-- delete all then insert 100,000
42076    39826    5.65%

如果您也親自測試一下可以很明顯的發現：結果對於證明起到的作用很小。