SQL Server Cursors: Working with Database Loops

Iterative queries or cursors in SQL Server are looping constructs that are built inside the SQL Server database engine. Cursors come with a host of configurable features, which can change their function and indeed suitability to certain tasks. A function common to all cursors is to fetch a bunch of data, loop through each record, and modify the values if necessary. In almost every case, SQL Server’s set based operations would provide the better performance solution. However, there are times when there is a requirement to step through each row individually, and cursors have been optimized in SQL Server since 2008 to fulfill that requirement.

New to SQL Server and want to learn more? Take a class at Udemy.com

In a relational database, operations are carried out on expressions and columns within a table that consist of sets of rows. The SELECT statement returns all rows that have a value that satisfies the conditions within the select statement. The results are termed a result set.

This method is not always suitable for all applications. Some applications such as an online ecommerce site with product catalogues may require only a small subset of the result set. These applications would prefer to work on single or small sets of rows, preferably one at a time. This is the typical usage for cursors. The cursor loads the result set, or copies the rows into memory or a virtual table (tempdb), then performs multiple operations in a row-by-row manner.

Get a better understanding of cursors at Udemy.com

As a result, cursors in SQL Server are described as being an iterative query, or a database object used by applications to manipulate data in a set on a row-by-row basis. Cursors are commonly compared to, and contrasted with, WHILE loops, which perform a similar function.

Creating a Cursor

The first step in creating a cursor in SQL Server is to choose which method to use. Programmers create cursors in SQL Server either by using the T-SQL language, or via the database’s application-programming interface. In either case, the steps are similar and only the syntax differs.

This article will focus on T-SQL syntax.

The procedural steps required to create the cursor are as follows

1. Define the characteristics of the cursor
2. Associate the cursor with a T-SQL statement’s result set and open the cursor
3. Retrieve the rows into the cursor
4. Read or Perform modification of a row at a current position
5. Close the cursor

Declaring and Defining a Cursor

DECLARE

Use the DECLARE statement to create the cursor.

Syntax: DECLARE Cursor_name CURSOR FOR select_statement;

With the cursor and its associated SELECT statement assigned and defined, it is ready to be activated.

OPEN

Syntax: OPEN cursor_name;

Once the cursor is open and available for use it is now possible to fetch the results of the SELECT statement associated with the cursor.

FETCH

Syntax: FETCH cursor_name;

The FETCH clause retrieves the result set and stores the rows in the cursor object. The result set is available until the cursor is closed.

CLOSE

Syntax: CLOSE cursor_name;

The close statement is used to temporarily close a cursor. When the cursor is re-opened it will need to once more fetch the result set from the tables.

DEALLOCATE

Syntax: DEALLOCATE cursor_name;

The de-allocate clause is used to remove the cursor permanently. It will remove all reference to the object.

Cursor Operation

When the cursor first opens, it will be pointing towards the first row. By utilizing the FETCH clause in T-SQL the cursor can retrieve data one row at a time. However, FETCH is flexible and it can go backwards and forwards and jump to specific rows based on its current position. FETCH-NEXT is the default method, to use another option, requires them to be defined when the cursor is created.

FETCH–FIRST: Fetches the first row in the cursor

FETCH-NEXT: Fetches the next row in the cursor (This is the default behavior)

FETCH-PRIOR: Fetches the row before the previously fetched row

FETCH-LAST: Fetches the row at the end

FETCH-ABSOLUTE: Fetches the nth row in a cursor if n is positive, if n is negative it fetches the nth row before the previously fetched row

FETCH-RELATIVE: Fetches the nth row from the previously fetched row if positive, if negative fetches the nth row before the previously fetched row

SQL Server Cursor Types

The type of cursor created can be very important and should match the requirements. Cursors are typically refined and customized to be fit for purpose at the time they are created. They are tuned for performance and function using single or combinations of configuration option switches.

Insensitive

Defines a cursor that makes a copy of the data and uses this working data in tempdb to manipulate and answer queries.

Scroll

 

Specifies that all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available.

 

Read-Only

Creates a read-only cursor, which prevents modification or updates through the cursor

Optimistic

Cursor updates do not succeed if the original data has changed since it was uploaded into the cursor

Scroll-lock

Guarantees that changes made through the cursor will succeed because SQL Server locks the rows when they are read into the cursor making them available for future update

Forward-only

Is a cursor whose only method of travel is forward through the rows, using FETCH-Next.

Fast Forward

This cursor is the fastest cursor going it is a combination of the read-only and forward-only cursors

 

Key Set

Specifies a cursor whose membership and order of rows within the cursor are fixed at the time of opening. Set of rows and corresponding keys are built into a table tempdb and are known as the keyset.

Local

The cursor has only local significance and scope to the batch, stored procedure or trigger. The cursor is implicitly de-allocated by the system if the batch, stored procedure or trigger terminates.

Cursors are flexible, with many options and can be quickly created and utilized to perform specialist functions. They are certainly not likely to be considered for most routine tasks due to their inherent low performance compared to the set based operations, and their high processor and IO overheads. However, for some specialist tasks that require row-by-row handling, cursors may well still have a role to play.

To learn more about writing SQL, take a class at Udemy.com