接下来，我们使用其他类型的游标进行测试，从它们的测试结果会发现：
当使用STATIC、KEYSET、READ_ONLY、FAST_FORWARD类型的游标，可以得到理想的执行计划(索引S_AUDIT_ITEM_M3上使用索引查找)。
但是，如果使用其他第二类游标类型，得到的执行计划就不甚理想了(索引S_AUDIT_ITEM_M4上使用索引扫描)。
从上面的测试，我们知道STATIC、KEYSET、READ_ONLY及FAST_FORWARD游标可以带给我们同样的理想结果。那么，这些游标有什么共同点?
我们可以分析一下两大游标类型执行计划的不同：
1. STATIC、KEYSET、READ_ONLY、FAST_FORWARD类型游标的执行计划：
Executes StmtText
-------------------- --------------------------------------------------------------------------------------------------------------------
1 OPEN curTest
1 |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[CONFLICT_ID] as
1 |--Sequence Project(DEFINE:([Expr1008]=i4_row_number))
1 |--Segment
1 |--Sort(ORDER BY:([T1].[OPERATION_DT] ASC))
1 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[U
1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]) OPTIMIZED)
1 | |--Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M3] AS [T1]),
4 | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1]
4 |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))
Executes StmtText StmtId NodeId
-------------------- ----------------------------------------------------------------------------------------------- ----------- --------
1 FETCH NEXT FROM curTest INTO @CONFLICT_ID 2 1
1 |--Clustered Index Seek(OBJECT:(CWT), SEEK:([CWT].[ROWID]=FETCH_RANGE((0))) ORDERED FORWARD) 2 2
-------------------- --------------------------------------------------------------------------------------------------------------------
1 OPEN curTest
1 |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[CONFLICT_ID] as
1 |--Sequence Project(DEFINE:([Expr1008]=i4_row_number))
1 |--Segment
1 |--Sort(ORDER BY:([T1].[OPERATION_DT] ASC))
1 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[U
1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]) OPTIMIZED)
1 | |--Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M3] AS [T1]),
4 | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1]
4 |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))
Executes StmtText StmtId NodeId
-------------------- ----------------------------------------------------------------------------------------------- ----------- --------
1 FETCH NEXT FROM curTest INTO @CONFLICT_ID 2 1
1 |--Clustered Index Seek(OBJECT:(CWT), SEEK:([CWT].[ROWID]=FETCH_RANGE((0))) ORDERED FORWARD) 2 2
2. dynamic类型游标的执行计划
Executes StmtText
---------------------------------------------------------------------------------------------------------------------------------------
1 FETCH NEXT FROM curTest
1 |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[ROW_ID] as [T1].[ROW_ID]
1 |--Compute Scalar(DEFINE:([Expr1008]=CWT_ROWID()))
1 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[USER_ID]=[testcurso
1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]))
1 | |--Index Scan(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M4] AS [T1]), ORDERED BACKWARD)
1007751 | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS [T1]), SEEK:([T
1 |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))
---------------------------------------------------------------------------------------------------------------------------------------
1 FETCH NEXT FROM curTest
1 |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[ROW_ID] as [T1].[ROW_ID]
1 |--Compute Scalar(DEFINE:([Expr1008]=CWT_ROWID()))
1 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[USER_ID]=[testcurso
1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]))
1 | |--Index Scan(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M4] AS [T1]), ORDERED BACKWARD)
1007751 | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS [T1]), SEEK:([T
1 |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))
比较一下两个执行计划的FETCH NEXT部分(SQL Server在游标打开阶段不会读取表)：在第一个执行计划中，FETCH是直接从临时对象CWT中得到行，然后从CWT.ROWID中找到相应范围。而在第二个计划中，FETCH是动态的而且是真正对表进行了读取，从表中取得数据。
