游标一次只作用一行，它的速度明显慢于相应的集操作

--游标一次只作用一行，它的速度明显慢于相应的集操作

CREATE TABLE t1 (c1 int primary key ,c2 int ,c3 char(8000))
GO
---Load 6000 rows into this table as follows
DECLARE @i int
SELECT @i=0
WHILE(@i<6000)
BEGIN
INSERT INTO t1 VALUES(@i,@i+1000,'hello')
SET @i=@i +1
END

--方法一：仅用一条语句更新表格的所有行
BEGIN TRAN
UPDATE t1 SET c2=1000+c2
COMMIT TRAN

--方法二：用游标更新表格的所有行
DECLARE mycursor CURSOR FOR
    SELECT c2 FROM t1
OPEN mycursor
--GO
BEGIN TRAN
    FETCH mycursor
    WHILE(@@FETCH_STATUS=0)
    BEGIN
        UPDATE t1 SET c2=1000+c2 WHERE CURRENT OF mycursor
        FETCH mycursor
    END
COMMIT TRAN
CLOSE mycursor
DEALLOCATE mycursor

--Now query the total work time
SELECT TOP 10
total_worker_time/execution_count as avg_cpu_cost,
execution_count,
    (
    SELECT SUBSTRING(text,statement_start_offset/2+1,
        (CASE WHEN statement_end_offset=-1
            THEN LEN(CONVERT(NVARCHAR(MAX),text))*2
            ELSE statement_end_offset
            END
        -statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats ORDER BY avg_cpu_cost DESC

 

 

Will run a number of times (slightly more than 100) then fail with:

The query has exceeded the maximum number of resultsets that can be displayed in the results grid. Only the first 100result sets are displayed in the grid.

The SSMS has a limit on the number of record-sets it can show you.One quick way to by-pass that limitation is to press Ctrl+T (or menuQuery->Results to->Results to Text) to force the output to be inplain text, rather than table-like recordsets. You'll reach anotherlimit eventually (the results window can't handle an infinite amount oftext output) yet it will be far greater.

In the sample above you don't get the error after changing the results to be in text form!