Best method for maintaining list

SQL Apprentice Question
I am wondering what, if any, method would best suit the following request.

I want to maintain a list of items in SQL Server.
I'm thinking a Stored Procedure.


The list is of two columns: (both VarChar)
colName, colTag


data:
Name1, Tag1
Name2, Tag2
Name3, Tag3
Name5, Tag4


Now, before anyone says, why not a table, I would answer that I want to be
able to add/move line items around easily. And I don't want to have to
renumber an ID column of any type.
So If I wanted to quickly add a new row to this data between Name2 and
Name3, I don't want to have to open up Enterprise explorer to do it.
This list is for pulling into a .NET application.


Any ideas would be great.



Celko Answers
>> I want to maintain a list of items in SQL Server. <<


SQL has no list structure. It also has no pointers with which to
create such a thing.


>> The list is of two columns: (both VarChar) colName, colTag <<


Why did you put that silly, redundant prefix on the column names?
People who know RDBMS, data modeling and ISO-11179 Standards do not do
this!


>> Now, before anyone says, why not a table, I would answer that I want to be able to add/move line items around easily. <<


Since the table is the ONLY data structure in SQL, you either need to
model your problem in SQL or you need to use LISP or some other
language that has list structures.


>> And I don't want to have to renumber an ID column of any type. <<


But you do not mind the prospect of the pointer chains in list
manipulations? Which also leads to the quesitions, where the DDL? And
what is the key?


>> So If I wanted to quickly add a new row to this data between Name2 and Name3, I don't want to have to open up Enterprise explorer to do it. <<


You might want to look up the "Information Principle" when you finally
study RDBMS. All information is represented as values in columns --
NOT by something physical in the storage.

Rows are not records; fields are not columns; tables are not files;
there is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless. If you want an ordering,
then you need to have a column that defines that ordering.



>> This list is for pulling into a .NET application. <<


Why are trying to design the database to fit an application? That is
what we did 40+ yers ago when we had file systems and procedural
languages, not RDBMS and tiered architectures.

Given a table with an ordering column, position, like this:


CREATE TABLE Foobar
(display_position INTEGER NOT NULL PRIMARY KEY
CHECK (display_position > 0),
foo_value CHAR(10) NOT NULL);


Re-arrange the display order of foo_value based on the position column:


CREATE PROCEDURE SwapFoobars (@old_position INTEGER, @new_position
INTEGER)
AS
UPDATE Foobar
SET display_position
= CASE display_position
WHEN @old_position
THEN @new_position
ELSE display_position + SIGN(@old_position - @new_pos)
END
WHERE display_position BETWEEN @old_position AND @new_position
OR display_position BETWEEN @new_position AND @old_position;


When you want to drop a few rows, remember to close the gaps with this:


CREATE PROCEDURE CloseFoobarGaps ()
AS
UPDATE Foobar
SET display_position
= (SELECT COUNT (F1.position)
FROM Foobar AS F1
WHERE F1.display_position <= Foobar.display_position);


Is this so hard?