SQL Server cursor usage

The SQL Server DML merge method (select, update, and delete) isData SetData processing units are convenient and efficient, while cursor usesReporterIn order to handle materials, there is a high degree of attention to material operation.
The base structure of SQL cursor.

123456789101112131415161718192021222324

DECLARE curTemp CURSOR-- Declare cursor and its data source  FOR      (        SELECT Col1,Col2,Col3......        FROM SourceTable        WHERE Condition      ) OPEN curTemp -- Open the cursor and establish the relationship between the cursor and the data table. DECLARE @var1 as .....DECLARE @var2 as ..... FETCH NEXT FROM curTemp INTO @var1,@var2 -- Store data in the changing data  WHILE (@@FETCH_STATUS = 0) -- Check whether there is any token    BEGIN        ....................        FETCH NEXT FROM curTemp INTO @var1,@var2      END CLOSE curTemp -- Relationship with cursor and relationship between cursor and the data tableDEALLOCATE curTemp -- Remove the cursor object

When Using cursor, SQL Server may suffer from poor performance and should be the last method. However, when using cursor, pay attention to the following:

1. The amount of metadata should be retrieved from the backend. forward_only and fetch next are the preset values. Do not use the limit methods such as scroll and fetch prior, fetch first, and fetch last, individual users usually set fast_forward to improve efficiency.
2. Do not use cursor to modify or delete data. You can explicitly specify read_only metadata.

Judge the last item in cursorUse @ cursor_rows to check the amount of metadata in the last open cursor.

12345678910111213141516171819202122232425262728293031323334

DECLARE @Temp Table (EmpNO char(5),EmpName nchar(8))INSERt INTO @Temp VALUES ('00001','Issue 3')INSERt INTO @Temp VALUES ('00002','Li si')INSERt INTO @Temp VALUES ('00003','Wang 5') -- Cursor T-SQL extension Embedding MethodDECLARE curTemp CURSOR STATIC -- Declare this cursor as static  FOR     (       SELECT EmpNO,EmpName       FROM @Temp       WHERE EmpNO IN ('00001','00002')     ) OPEN curTemp  DECLARE @EmpNO as char(5),@EmpName nchar(8)DECLARE @Count as smallint SET @Count = 0 -- Set a Metric FETCH NEXT FROM curTemp INTO @EmpNO,@EmpName  WHILE (@@FETCH_STATUS = 0)    BEGIN       SET @Count = @Count + 1        IF @Count = @@CURSOR_ROWS          PRINT 'The last response is' + @EmpName          FETCH NEXT FROM curTemp INTO @EmpNO,@EmpName    END  CLOSE curTempDEALLOCATE curTemp

Description:

1. Because the index of dynamic data reflects all changes, the data column in the cursor does not change, @ cursor_rows never explicitly specifies the number of data columns in the cursor. Therefore, you must set cursor to static ).
2. When cursor is declared as static, the data will be first placed in tempdb, and the amount of data should be small to avoid the rapid increase of tempdb, affecting the performance.

Msdn Materials
• Declare curosr, fetch, @ fetch_status, @ cursor_rows
• Selection criteria