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.
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.
For more information on cursors, also take a look at the free SQL query training provided by Steve Stedman.
In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time.
(Note: These examples do no real processing.) I take the idcol field from the record and print it to the screen for each record in the table — simple stuff. As noted in the section on cursor types above, this is a combination of a READ_ONLY and a FORWARD_ONLY cursor.
To get a true look at how many reads are performed on the database, I'll need to use SQL Server Profiler.
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.
WHILE loops are as easy to use as a cursor, but sometimes these constructs are a little more difficult to read and/or to understand, as a query must occur on the base table(s) that must fetch a single next row.
This type of operation is very tricky to do in situations where your underlying base table does not have a primary key column (which it should have).
This will essentially make it so that a search for a single value based on the primary key will have to traverse a couple of data pages before the value is found.