Sql procedure loop while updating records using cursors
This tool will give me a combined aggregation of the reads taken for each record rather than each individual record, as would be indicated through SET STATISTICS IO ON.
The cursor example above produces 11114 logical reads from the database.
The following snippet performs the same operation as the cursor above but through the use of a WHILE loop.
Notice that it is necessary to perform a query on the base table for each record returned.
Notice the fld3 field in the table declaration; I'm making this a larger field so that fewer rows are created on a page of data.
In my opinion, this will make the test a little more realistic, as it will "simulate" a table that contains a variety of character columns along with some additional fields of data.
Store; OPEN @Business Cursor; FETCH NEXT FROM @Business Cursor INTO @Business Entity ID, @Business Name; WHILE @@FETCH_STATUS = 0 BEGIN PRINT cast(@Business Entity ID as VARCHAR (50)) ' ' @Business Name; FETCH NEXT FROM @Business Cursor INTO @Business Entity ID, @Business Name; END CLOSE @Business Cursor; DEALLOCATE @Business Cursor; This should give you a quick overview of how to quickly build and use a cursor on Microsoft SQL Server.
Also, a majority of cursor operations occur in tempdb, so a heavily used tempdb will be even more overloaded with the use of cursors.One advantage of the WHILE loop is that no objects must be created in memory to facilitate the looping through a set of records as is necessary with a cursor. Let's set up an example and take a look at a couple of specific scenarios.The code snippet below creates and populates our table for testing our two constructs.It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL script then a cursor is one way of doing it.Note: If you are new to SQL Server and come from an Oracle background, you should know that cursors on SQL Server are different from those on Oracle.