Traverse an MSSQL Table without using CURSOR

Introduction

The problem with the above tip is that it involves the modification of the underlying table schema. A better solution would be to run the traversal over the designed table schema, utilising the primary key value as a traversal key.

The following tip requires a sortable primary key.

Table Creation

if object_id('dbo.SomeTable', 'U') is not null
    drop table dbo.SomeTable;
    
create table dbo.SomeTable
(
     id     int identity(1, 1) not null primary key  - Used as Traversal Key
    ,Value  int not null
); 

Traversal Code

declare @id int = 0;  -- Initialised to a value smaller than the initial traversal key value.

while exists (select top 1 id from SomeTable where id > @id)
begin
    declare @value  int;
    
    select top 1 
         @value = value
        --,OtherFields
        ,@id = id
    from SomeTable
    where id > @Id
    order by id;

    -- Do something with @value and other fields.

    print @value;
end 

The benefit of this means you are no longer modifying the underlying schema of the table, and because of the sort operation, you can use any field as the traversal key.

The only requirement is that the traversal key must be unique.

Simple Table Population Code

insert into SomeTable (Value)
values
 (4) 
,(8)
,(1)