Row-by-Row Processing - SQL Server Query Performance Tuning - page 463

This is not how you normally access databases from PowerShell, but it does show how a client-side cursor
operates. Note that the table has two rows with the size of each row equal to 1,000 bytes (=4 bytes for INT + 996 bytes
for CHAR(996) ) without considering the internal overhead. Therefore, the size of the complete result set returned by
the SELECT statement is approximately 2,000 bytes (= 2 x 1,000 bytes).
On execution of the cursor open statement ( $AdoRecordset.Open() ), a default result set is created on the client
machine running the code. The default result set holds as many rows as the client-network buffer can cache.
Since the size of the result set is small enough to be cached by the client-network buffer, all the cursor rows are
cached on the client machine during the cursor open statement itself, without retaining any lock on the
dbo.Test1 table. You can verify the lock status for the connection using the sys.dm_tran_locks dynamic
management view. During the complete cursor operation, the only request from the client to SQL Server is the
SELECT statement associated to the cursor, as shown in the Extended Events output in Figure 22-1 .
Figure 22-1. Profiler trace output showing database requests made by the default result set
To find out the effect of a large result set on the default result set processing, let's add some more rows to
the test table.
SELECT TOP 100000
IDENTITY( INT,1,1 ) AS n
INTO #Tally
FROM Master.dbo.syscolumns scl,
Master.dbo.syscolumns sc2;
INSERT INTO dbo.Test1
(C1, C2)
SELECT n,
n
FROM #Tally AS t;
GO