Linked List in SQL Linked List in SQL sql sql

Linked List in SQL


create a table with two self referencing columns PreviousID and NextID. If the item is the first thing in the list PreviousID will be null, if it is the last, NextID will be null. The SQL will look something like this:

create table tblDummy{     PKColumn     int     not null,      PreviousID     int     null,      DataColumn1     varchar(50)     not null,      DataColumn2     varchar(50)     not null,       DataColumn3     varchar(50)     not null,      DataColumn4     varchar(50)     not null,      DataColumn5     varchar(50)     not null,      DataColumn6     varchar(50)     not null,      DataColumn7     varchar(50)     not null,      NextID     int     null}


Using Adrian's solution, but instead of incrementing by 1, increment by 10 or even 100. Then insertions can be calculated at half of the difference of what you're inserting between without having to update everything below the insertion. Pick a number large enough to handle your average number of insertions - if its too small then you'll have to fall back to updating all rows with a higher position during an insertion.


Store an integer column in your table called 'position'. Record a 0 for the first item in your list, a 1 for the second item, etc. Index that column in your database, and when you want to pull your values out, sort by that column.

 alter table linked_list add column position integer not null default 0; alter table linked_list add index position_index (position); select * from linked_list order by position;

To insert a value at index 3, modify the positions of rows 3 and above, and then insert:

 update linked_list set position = position + 1 where position >= 3; insert into linked_list (my_value, position) values ("new value", 3);