Monday, February 21, 2011

SQL Server: When We Should Use Read Only Cursors

Everyone of us knows that processing of data through cursors, is a worst choice, because SQL Server are designed to work best with sets of data and not one row at a time.
Still processing through cursors can be made faster by making little changes. Like, if we need to use cursor to process one row at a time and we don’t need to update base table through this cursor, we MUST use read only cursor. As read only cursors are non-updateable so no locks are required on the base table. Only shared locks are held. And due to this phenomenon read only type of cursor are considered FASTER and SAFER.
Syntax is almost same to ordinary cursor and only keyword of READ_ONLY is added.

      FOR SELECT columnNameHere
            FROM  tableNameHere
            WHERE filterConditionHere

OPEN YourCursorNameHere
FETCH NEXT FROM YourCursorNameHere INTO @parameterName
      FETCH NEXT FROM YourCursorNameHERE INTO @parameterName
CLOSE YourCursorNameHere
DEALLOCATE YourCursorNameHere


  1. The READ ONLY clause of the DECLARE CURSOR statement is an ISO compliant syntax (according to SQL Books Online), but possibly not in the common context you have shown. To enable the read only option in the keyword position following the keyword CURSOR and preceding the SQL SELECT statement, the keyword READ_ONLY (with an underscore) must be used (according to SQL Books Online - a T-SQL extended syntax - and not ISO syntax). Your syntax as shown (using a space instead of an underscore) will likely generate an error.

    The ISO cursor syntax allows the use of the READ ONLY keyword (with a space instead of an underscore, as you showed in your example) in a different position within the overall DECLARE CURSOR statement - in a FOR clause following the SQL SELECT statement.

    It is a minor, nitpick difference, but good to be aware of to avoid an error. I learned something new here.


    Scott R.

  2. Thanks Scott for pointing out a big mistake. Yes its READ_ONLY and i missed "_".
    Post Updated

  3. Good day, but does the READ_ONLY Cursor type work in Sql sERVER 2008. I am getting an error message that says 'READ_ONLY' is not a recognized option.

  4. READ_ONLY option is available for comparability level 80


All suggestions are welcome