HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.newusers » Best Way to Implement Row Order
Topic: Best Way to Implement Row Order
I have a requirement to provide users with the ability to control the order
of items in a table.
Requiring them to enter a number into a new column, which I would then sort
by, is not a valid option as it becomes too messy when they are adding,
deleting, or changing the order of items. Ideally, I would have an UP/DOWN
button for each item, and items would order as created but could then be
moved around using these buttons.
Does anyone have suggestions on an efficient way to implement this? I would
really like to minimize the number of database hits in implementing this.
Thanks!
--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com
Replies below ↓
Replies
Re: Best Way to Implement Row Order
Jonathan Wood wrote:
> I have a requirement to provide users with the ability to control the
> order of items in a table.
Your requirement is the presentation layer not the storage layer
> Requiring them to enter a number into a new column, which I would then
> sort by, is not a valid option as it becomes too messy when they are
> adding, deleting, or changing the order of items. Ideally, I would have
> an UP/DOWN button for each item, and items would order as created but
> could then be moved around using these buttons.
Again presentation layer issues. Not how you store/retrieve from SQL.
> Does anyone have suggestions on an efficient way to implement this? I
> would really like to minimize the number of database hits in
> implementing this.
Whomever is giving you this requirement, saw this somewhere. Can you post
an example URL? Perhaps someone will be able to point you in an efficient
direction. Is this to be a .net app or a web app?
--
WDV
Re: Best Way to Implement Row Order
user@domain.invalid,
>> I have a requirement to provide users with the ability to control the
>> order of items in a table.
>
> Your requirement is the presentation layer not the storage layer
Not from my perspective. I'll need additional data fields to represent this
order. In addition, I need an efficient method of modifying those additional
data fields. The algorithm is highly dependent on the underlying storage
layout.
> Whomever is giving you this requirement, saw this somewhere. Can you post
> an example URL? Perhaps someone will be able to point you in an efficient
> direction. Is this to be a .net app or a web app?
It's an ASP.NET application. I was thinking (incorrectly?) that the concept
was pretty straight forward as I've implemented stuff like this in desktop
applications many times. But, here, database hits is more of a concern.
Does anyone know if SQL has any commands to make swapping the values from
the same column between two different rows more efficient? Swapping the
order of two items would otherwise mean reading one row, reading the second
row, writing the second row, and finally writing the first row. And of
course one also needs to consider concurrency issues during this process.
Some type of SWAP command would be really nice here.
--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com
Re: Best Way to Implement Row Order
As far as efficiency goes, any method would, of course, have to read both
rows and write both rows back. For concurrency, you could either use
transactions with the appropriate transaction isolation level or update both
rows with one UPDATE statement, something like the following:
Set NoCount On
Create Table #TestTable (PK int Primary Key, DataValue varchar(25) Null)
Insert #TestTable (PK, DataValue) Values (1, 'TestA')
Insert #TestTable (PK, DataValue) Values (2, Null)
Insert #TestTable (PK, DataValue) Values (3, 'Sample')
/* Now swap the DataValues for the rows with PK=2 and PK=3 */
Declare @Key1 int, @Key2 int
Set @Key1 = 2
Set @Key2 = 3
Update T
Set DataValue = T1.DataValue
From #TestTable T
Inner Join #TestTable T1 On T.PK <> T1.PK
Where T.PK In (@Key1, @Key2)
And T1.PK In (@Key1, @Key2)
Select * From #TestTable
Drop Table #TestTable
Tom
"Jonathan Wood" <jwood@softcircuits.com> wrote in message
news:e%23vRPlcuIHA.4876@TK2MSFTNGP02.phx.gbl...
> user@domain.invalid,
>
>>> I have a requirement to provide users with the ability to control the
>>> order of items in a table.
>>
>> Your requirement is the presentation layer not the storage layer
>
> Not from my perspective. I'll need additional data fields to represent
> this order. In addition, I need an efficient method of modifying those
> additional data fields. The algorithm is highly dependent on the
> underlying storage layout.
>
>> Whomever is giving you this requirement, saw this somewhere. Can you post
>> an example URL? Perhaps someone will be able to point you in an efficient
>> direction. Is this to be a .net app or a web app?
>
> It's an ASP.NET application. I was thinking (incorrectly?) that the
> concept was pretty straight forward as I've implemented stuff like this in
> desktop applications many times. But, here, database hits is more of a
> concern.
>
> Does anyone know if SQL has any commands to make swapping the values from
> the same column between two different rows more efficient? Swapping the
> order of two items would otherwise mean reading one row, reading the
> second row, writing the second row, and finally writing the first row. And
> of course one also needs to consider concurrency issues during this
> process. Some type of SWAP command would be really nice here.
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>
Re: Best Way to Implement Row Order
Tom Cooper wrote:
> As far as efficiency goes, any method would, of course, have to read both
> rows and write both rows back. For concurrency, you could either use
> transactions with the appropriate transaction isolation level or update both
> rows with one UPDATE statement, something like the following:
>
> Set NoCount On
> Create Table #TestTable (PK int Primary Key, DataValue varchar(25) Null)
> Insert #TestTable (PK, DataValue) Values (1, 'TestA')
> Insert #TestTable (PK, DataValue) Values (2, Null)
> Insert #TestTable (PK, DataValue) Values (3, 'Sample')
>
> /* Now swap the DataValues for the rows with PK=2 and PK=3 */
>
> Declare @Key1 int, @Key2 int
> Set @Key1 = 2
> Set @Key2 = 3
>
> Update T
> Set DataValue = T1.DataValue
> From #TestTable T
> Inner Join #TestTable T1 On T.PK <> T1.PK
> Where T.PK In (@Key1, @Key2)
> And T1.PK In (@Key1, @Key2)
>
> Select * From #TestTable
See, here's where I get the disconnect coming from other DBMS's.
I thought you are never to assume that because the records come to you
in one order, to depend upon that order.
If you used an ORDER BY clause with the above SELECT statement, they should
arrive in proper order according to the resolution of the ORDER BY, and
duplicates of those can arrive in random fashion.
Secondly...
Since the application can have inserts between records, you have to rewrite all
the pointers after each insertion, or have some way to deal with 256K inserts
between TestA and Sample.
My head is already hurting with scaleablity problems. I'm interested how you
solve this problem.
> Drop Table #TestTable
>
> Tom
>
> "Jonathan Wood" <jwood@softcircuits.com> wrote in message
> news:e%23vRPlcuIHA.4876@TK2MSFTNGP02.phx.gbl...
>
>>user@domain.invalid,
>>
>>
>>>>I have a requirement to provide users with the ability to control the
>>>>order of items in a table.
>>>
>>>Your requirement is the presentation layer not the storage layer
>>
>>Not from my perspective. I'll need additional data fields to represent
>>this order. In addition, I need an efficient method of modifying those
>>additional data fields. The algorithm is highly dependent on the
>>underlying storage layout.
>>
>>
>>>Whomever is giving you this requirement, saw this somewhere. Can you post
>>>an example URL? Perhaps someone will be able to point you in an efficient
>>>direction. Is this to be a .net app or a web app?
>>
>>It's an ASP.NET application. I was thinking (incorrectly?) that the
>>concept was pretty straight forward as I've implemented stuff like this in
>>desktop applications many times. But, here, database hits is more of a
>>concern.
>>
>>Does anyone know if SQL has any commands to make swapping the values from
>>the same column between two different rows more efficient? Swapping the
>>order of two items would otherwise mean reading one row, reading the
>>second row, writing the second row, and finally writing the first row. And
>>of course one also needs to consider concurrency issues during this
>>process. Some type of SWAP command would be really nice here.
>>
>>--
>>Jonathan Wood
>>SoftCircuits Programming
>>http://www.softcircuits.com
>>
>
>
>