Cursors T-SQL

The definition of the word "cursor” can actually be a little confusing. This is because there are several meanings that have similar context. Before going any further, we'd like to clarify our use of this word.If asked, most computer users would likely tell you that the little blinking bar in a text editor is a cursor. It's a placeholder or position pointer. You could reason that after a SQL query is executed, the cursor would be a pointer to the current record. As much as this makes sense, this isn't the definition of a cursor in the SQL world. In this context, the word "cursor” is an acronym for CURrent Set Of Rows. A cursor is a set of records returned from a query.

Unfortunately, it can get a little more confusing than that. It is true that when a SELECT statement is executed, the database engine returns a cursor — or set of records — held in memory or streamed across a network connection. This type of cursor is the basis for programming objects such as record sets and data sets. In Transact-SQL, when you declare a cursor-type variable, a result set is read into the memory managed by this object for the purpose of iterating through each record. We know — this actually takes us back to the original definition of a cursor as a single record pointer. The fact is that the cursor represents the entire set of records and supports navigation, one record at a time.

Rowset Versus Cursor Operations

Using a cursor to manage or manipulate a set of records is very different from the usual approach.It requires the query processing engine to give up a lot of its built-in optimizations and to hand over a set of data so that you can manage it yourself. Although this may afford you a little more flexibility, it usually comes at a cost of performance and efficiency. There are cases where using a cursor will solve an important business problem. However, it has been my experience that often when I thought I needed to use a cursor to solve a problem, there was a simpler and more efficient method using a standard set-based query. For example, the new MERGE command may eliminate a lot of older cursor code used to loop through records, performing related inserts, updates, and deletes. The MERGE command is introduced in Chapter (Transactions).

Probably one of the greatest challenges for programmers learning to use SQL is the different approach usually taken to handling data. In procedural and object-oriented programming, developers typically work with objects one at a time. Groups of objects are organized into collections, structures, or arrays that support looping and enumeration. So far, you've seen that SQL Server returns sets of rows, all at once, and is optimized to support this paradigm rather than one record at a time. SQL Server performs best when using native set-based operations. There may be times when you will need to loop through a set of individual records, but be forewarned that cursor operations are typically the worst performing operations that occur in T-SQL. If there is a set-based solution that can provide the same functionality as the cursor, you should choose the set-based solution.

T-SQL cursors' default behavior is forward-only and up datable, unlike their counterparts used in other programming languages, which typically default to forward-only read-only. This makes the T-SQL cursor even more expensive as far as performance is concerned. When a cursor is up datable it means that the cursor maintains a link to the underlying data from which it was built. If the underlying data changes, so will the contents of the cursor. To prevent this behavior, the cursor can be declared with an INSENSITIVE option that makes it read-only and decreases the amount of resources needed to maintain it. Another method of mitigating the cost of a cursor is to mark only specific columns in the cursor as up datable rather than the default of all the columns. If you must use a cursor, keep this in mind and use the least expensive cursor possible. The examples used in this chapter follow these performance guidelines.

Creating and Navigating a Cursor

The first order of business is to declare a cursor-type variable. Because this is a special type of non-scalar object, the variable name isn't prefixed with an at symbol (@). The cursor variable can be declared and defined on the same line with the SELECT statement used to populate the cursor:

DECLARE curProduct INSENSITIVE CURSOR FOR SELECT ProductID, Name FROM Production.Product

To create a cursor that allows updates only to and from the Name column of the Product table, declare the cursor like this:

DECLARE curProduct CURSOR FOR SELECT ProductID, Name FROM Production.Product FOR UPDATE OF Name

The cursor isn't actually populated until it is opened. Executing the OPEN command loads the cursor structure and data into memory:

OPEN curProduct

At this point, the record pointer is positioned before the first row. The FETCH NEXT command navigates the pointer to the next record and returns a comma-delimited list of column values. In this case, the pointer is moved to the first row. Individual variables can be used to capture the values of the current row ' s column values:

DECLARE @ProdIDInt DECLARE @ProdNameVarChar(100) FETCH NEXT FROM curProduct INTO @ProdID, @ProdName

After FETCH NEXT is executed, one of two things will happen: the record pointer will either be positioned on a valid record or it will navigate beyond the last row. The state of the pointer can be determined using the global variable @@Fetch_Status. On a valid row, it returns 0; otherwise, it returns - 1 or - 2. It returns -1 if there is no next row to fetch. If a - 2 is returned, it means that the next row was deleted in the underlying table when using an updatable cursor. Using this variable, create a simple loop, navigating to the next record as long as @@Fetch_Status is equal to 0:

WHILE @@Fetch_Status = 0 BEGIN PRINT @ProdName FETCH NEXT FROM curProduct INTO @ProdID, @ProdName END

In this example, you're simply printing one of the variable values to the query results window. In production, you could use conditional statements to decide whether to perform related operations, such as inserting or deleting records. The real power of using cursors is in using them to conditionally call stored procedures. This way, you can use conditional logic to call different procedures under different conditions, and then a stored procedure can perform practically any combination of operations.

Finally, after navigating past the last record, it's necessary to do some cleanup. Use the CLOSE command to close the cursor, and then use the DEALLOCATE command to recover the memory used by the cursor:

CLOSE curProduct
DEALLOCATE curProduct

If we put it all together, here's the entire script:

DECLARE curProduct INSENSITIVE CURSOR FOR SELECT TOP 100 ProductID, Name FROM Production.Product DECLARE @ProdIDInt DECLARE @ProdNameVarChar(100) OPEN curProduct FETCH NEXT FROM curProduct INTO @ProdID, @ProdName WHILE @@Fetch_Status = 0 BEGIN PRINT @ProdName FETCH NEXT FROM curProduct INTO @ProdID, @ProdName END CLOSE curProduct DEALLOCATE curProduct

A list of product names is displayed in the query results pane for the first few products, as shown in Figure Below.
All-Purpose Bike Stand 5B Ball Bearing

query results pane for the first few products

Just for kicks, display the actual execution plan and run this query again. This should shed some light on any questions of efficiency when using cursors. Each FETCH NEXT command causes a separate look operation, which is slow and resource intensive. Figure (Show In Figure Below) shows only the first few of these operations. Cursors are powerful and give you procedural control over each row that T-SQL doesn't otherwise allow. Using a cursor is the only way to loop through individual rows and execute explicit code for each. But if there is any other way to do the job, you should consider any alternative to using cursors, especially with large sets of data.

Cursors-Figure


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

T-SQL Topics