Best method for maintaining list?

Hi All,
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.
Thanks,
John.You could create a stored procedure and return the values as shown below
SELECT 'Name1' as NameField,'Tag1' as TagField
UNION ALL
SELECT 'Name2' as NameField,'Tag2' as TagField
UNION ALL
SELECT 'Name3' as NameField,'Tag3' as TagField
UNION ALL
SELECT 'Name4' as NameField,'Tag4' as TagField
UNION ALL
SELECT 'Name1' as NameField,'Tag5' as TagField
"John" wrote:

> Hi All,
> 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.
> Thanks,
> John.
>
>|||>> 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.
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!
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.
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?
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.
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?|||If the data is persistant the generally, the best approach is to use a
table.
If you bought into the idea of hardcoding the values in a stored procedure,
you will have to deal with other issues -- not only you cannot alter them
without some convoluted logic, but adding and deleting items to the list
would amount to re-writing the procedure everytime.
Anith|||> 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.
You where coding using file systems in the mid/late 60's?
Interesting, they weren't even in manufacture by then, file systems where
only introduced in the mid/late 70's.
90% of the rest of your post is just crap and twoddle I won't even bother
commenting on.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149526125.455976.101630@.g10g2000cwb.googlegroups.com...
> SQL has no list structure. It also has no pointers with which to
> create such a thing.
>
> 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!
>
> 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.
>
> 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?
>
> 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.
>
> 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?
>|||Yup, that is what I was thinking too.
As it stands I create the actual list on the client side's objects.
I was just wondering if there was a mechanism on the SQL side of things that
I did not know about. If there was it would save me time if I needed to
append the listing, I could do it on the server.
Thanks for you help,
John.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OJjO0BMiGHA.3296@.TK2MSFTNGP05.phx.gbl...
> If the data is persistant the generally, the best approach is to use a
> table.
> If you bought into the idea of hardcoding the values in a stored
> procedure, you will have to deal with other issues -- not only you cannot
> alter them without some convoluted logic, but adding and deleting items to
> the list would amount to re-writing the procedure everytime.
> --
> Anith
>|||>> You were coding using file systems in the mid/late 60's? Interesting, th
ey weren't even in manufacture by then, file systems where only introduced i
n the mid/late 70's. <<
Why do you think that computers before 1960 had no files? That is
stilly.
When do you think that magnetic tapes and punch cards (aka "Unit Record
Equipment") were invented? Those tapes were the only thing we had in
the dark ages. Hard drives were insanely costly and were used for
sorting space and not data storage at first. .
Disk based systems came latter and were cheap enough in the mid-1970's
to be on mini-computers. The first DBMS I used was IMAGE/3000, whcih
was a version of TOTAL.|||> Why do you think that computers before 1960 had no files? That is
> stilly.
> When do you think that magnetic tapes and punch cards (aka "Unit Record
> Equipment") were invented? Those tapes were the only thing we had in
> the dark ages. Hard drives were insanely costly and were used for
> sorting space and not data storage at first. .
> Disk based systems came latter and were cheap enough in the mid-1970's
> to be on mini-computers. The first DBMS I used was IMAGE/3000, whcih
> was a version of TOTAL.
>
Tapes do not have "file systems" - a very big leap from a "file" which is a
unit of data, tapes have headers which contain amongst other things a data
marker.
Tapes only have a flat directory structure, they have no concept of
hierarchy organisation.
You probably need to read: http://en.wikipedia.org/wiki/File_system.

> the dark ages. Hard drives were insanely costly and were used for
> sorting space and not data storage at first. .
.... and where not available until early mid 70's or can you provide a lin
k
to disprove me?
According to your 'class of 65' you certainly weren't even in IT in the
early 60's and where only just leaving school!
http://www.baker.hs.org/65/celko-joe.html
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149971774.904725.271250@.h76g2000cwa.googlegroups.com...
> Why do you think that computers before 1960 had no files? That is
> stilly.
> When do you think that magnetic tapes and punch cards (aka "Unit Record
> Equipment") were invented? Those tapes were the only thing we had in
> the dark ages. Hard drives were insanely costly and were used for
> sorting space and not data storage at first. .
> Disk based systems came latter and were cheap enough in the mid-1970's
> to be on mini-computers. The first DBMS I used was IMAGE/3000, whcih
> was a version of TOTAL.
>