Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.
The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:
- Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
- Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
- Use the OPEN statement to execute the SELECT statement and populate the cursor.
- Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
- When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor’s result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is deallocated, you must issue a DECLARE statement to rebuild the cursor.
Advantages of Cursors:
1. Row by Row operations can be successfully executed.
Disadvantages of Cursors:
1. SQL Server by default have a direct performance degradation on the usage of cursors. SQL Server Engine processes records or data in such a way it will have a performance impact when cursor is used.
2. As you add overhead to the Server / engine, it will eat up your memory and hence other parallel processes will suffer too.
3. Cursors are the SLOWEST way to access data inside SQL Server as it does row by row operations and cursors are over thirty times slower than set based alternatives.
The following information may vary depending on the specific database system.
Fetching a row from the cursor may result in a network round trip each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE. Repeated network round trips can severely impact the speed of the operation using the cursor. Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.
Cursors allocate resources on the server, for instance locks, packages, processes, temporary storage, etc. For example, Microsoft SQL Server implements cursors by creating a temporary table and populating it with the query’s result-set. If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed. This wasting of resources on the server can not only lead to performance degradations but also to failures.
Considerations before using a Cursor:
1. Think of all possible alternatives to solve your problem and if you didnt got any then create a cursor.
2. Consider Temp tables as alternatives to usage of cursors.
Example:
This is a simple example of a cursor witch prints to the message screen of SQL Server.
USE AdventureWorksLT2008
— Declare a variable to save the product id.
DECLARE @ProductID int— Declare the Cursor
DECLARE cursornaam CURSOR FOR
— SELECT statement for getting a cursor record set
SELECT ProductID FROM SalesLT.Product WHERE Color = ‘Black’— Open cursor
OPEN cursornaam— fetch the first ID from the cursor
FETCH NEXT FROM cursornaam INTO @ProductID— While there is something to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN— Sub querys & Sub Declares
DECLARE @nameVar varchar(250)
SET @nameVar = (SELECT Name FROM SalesLT.Product Where ProductID = @ProductID )
Print @nameVar— try to fetch the next ID from the cursor
FETCH NEXT FROM cursornaam INTO @ProductID— end the actions
END
— Close the cursor, this can be reopened.
CLOSE cursornaam
–Deallocate all the resources of the cursor to free up space
DEALLOCATE cursornaam
