SQL Server – What Is A Cursor?

Hey Folks,

you might be aware of the Cursor. If not then don’t worry, I will try to give some heads up:

Cursors are database objects used to manipulate data in a set on a row-by-row basis; it acts just like a recordset in ASP and Visual Basic. We can also fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language. Before you can use a cursor, you need to declare it.

Cursor can also be defined as a set of rows together with a pointer that identifies a current row.

Syntax (Declaration of Cursor) and Arguments:

ISO Syntax OR SQL – 92 Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
   FOR select_statement 
   [FOR {READ ONLY | UPDATE [OF column_name [,...n ] ] } ]
   [;]

 

  • cursor_name: Itis the name of the Transact-SQL server cursor defined. It must conform to the rules for identifiers.
  • INSENSITIVE: It is a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
  • SCROLL: It specifies that all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. If SCROLL is not specified in an ISO DECLARE CURSOR, NEXT is the only fetch option supported. SCROLL cannot be specified if FAST_FORWARD is also specified.
  • select_statement:  It is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.
  • READ ONLY: It prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.
  • UPDATE [OF column_name [,…n]]: It defines updatable columns within the cursor. If OF column_name [,...n] is specified, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated. 

Transact-SQL Extended Syntax:

  • LOCAL: It specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter.
  • GLOBAL: Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect. 
  • FORWARD_ONLY: Itspecifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL.  
  • STATIC: It defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
  • KEYSET: It specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. 
  • DYNAMIC: It defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors. 
  • FAST_FORWARD: It specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. 
  • READ_ONLY: It prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated. 
  • SCROLL_LOCKS: It specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD or STATIC is also specified. 
  • OPTIMISTIC: It specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.
  • TYPE_WARNING: It specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another. 

 Steps for the Cursor Implementation with an example: 

  • Declare the variables for every attributes that we want to select
DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80)
  • Declare the Cursor
DECLARE vendor_cursor CURSOR FOR
  • Use the Select statement
SELECT BusinessEntityID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY BusinessEntityID;
  • Open the Cursor and fetch the contents in the same order as were in the Select statement
OPEN vendor_cursor;
FETCH NEXT FROM vendor_cursor 
INTO @vendor_id, @vendor_name;
  • It’s important to get the first row before the WHILE loop, so that the loop condition will be satisfied to start with. We use @@FETCH_STATUS built-in T-SQL variable which controls the exit condition. And at the end we use the same fetch content which we had used it previously.
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ';
SELECT @message = '----- Products From Vendor: ' + @vendor_name;
 
PRINT @message;
 
FETCH NEXT FROM vendor_cursor 
INTO @vendor_id, @vendor_name;
END
  • It becomes necessary to clean the resources up, which frees the memory space and improves the Server Up-Time
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Hope you got it understood well 🙂

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

7 Comments on “SQL Server – What Is A Cursor?”

  1. A wise man once said :

    ” Cursors are useful if you dont know SQL ”

    And that wise man IS NOT ME….Tongue out

  2. I’d be interested in seeing the difference between using a cursor and using a while loop against a table-typed variable.

  3. Excellent introduction to cursors Piyush, but I think we’re being short-sighted if we don’t also warn folks about the pitfalls of using cursors as well.

    TSQL is a set-based language, this means that practically anything that can be done with a cursor, can be done more efficiently using our traditional set-oriented techniques. Remember a cursor iterates through the dataset RBAR (row by agonizing row). In almost all cases, a set-based solution is going to be multitudes faster while using substantially less IO and CPU than going back and forth back and forth. This is even more true when the table is properly indexed for the intended operation. Cursors arer also extremely memory intensive by nature as well, sometimes using up all available memory and causing the process to use disk cache instead; a much slower operation. This is a similar concept to why we index our tables to achieve seeks instead of scans. Seeks get right to the appropriate rows while scans must do just that, scan every row in the dataset. Obviously not going to be as efficient in most cases.

    Cursors are great for small maintenance-type DBA tasks and for ad-hoc goals, but in general should never be used in a production environment. I’m not saying to never use them, just be cautious that a much more efficient set-based solution can usually be derived; particularily for production tasks.

  4. Which will be best in performance in cursor and while loop if i am iterating data from table?

    I also have Identity Type column in my sql table .

  5. I’m Satisfied from your tutorial. It was excellent and usefull.

    I like SQL and every thing which related it

  6. Hi there,

    Can Somebody tell me that how i can change a cursor to a simple select statement or something except cursor ?

    cheers

Leave a Reply

Your email address will not be published. Required fields are marked *