>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.
DECLARE YourCursorNameHere CURSOR READ_ONLY
      FOR SELECT columnNameHere
            FROM  tableNameHere
            WHERE filterConditionHere
OPEN YourCursorNameHere
FETCH NEXT FROM YourCursorNameHere INTO @parameterName
WHILE @@FETCH_STATUS = 0
      BEGIN
            YourImplementationHere
            .
            .
            .
      FETCH NEXT FROM YourCursorNameHERE INTO @parameterName
      END
CLOSE YourCursorNameHere
DEALLOCATE YourCursorNameHere
Advertisements

Posted on February 21, 2011, in Sql Server 2005, Sql Server 2008, T-SQL Cursors, TSQL Tips n Tricks. Bookmark the permalink. 4 Comments.

  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.Thanks,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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: