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
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.
ReplyDeleteThe 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.
Thanks Scott for pointing out a big mistake. Yes its READ_ONLY and i missed "_".
ReplyDeletePost Updated
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.
ReplyDeleteREAD_ONLY option is available for comparability level 80
ReplyDelete