2

I have two tables in a SQL Server database, that are in a many-to-many relationship, with the rows in TableA representing the 'container' structure in my business logic, and TableB the 'child' objects that can be included in any number of those containers. I've created a linking table, TableA_X_TableB, that consists of the columns:

TableA_PK UNIQUEIDENTIFIER, TableB_PK INT, Sequence INT

… with the last column used to record the sequence of TableB items within the TableA 'containers' — because these do, in fact need to be ordered lists.

All of my CRUD is very straightforward, except for this: when I delete an item out of the middle of a list, I would like SQL Server to 'seal the gap' in the sequence numbers that pertain to a particular TableA sequence. I.e., if I have six entries associated with a particular TableA_PK…

TableA_PK                            | TableB_PK | Sequence |
=============================================================
AD7D5099-A14D-48D4-9860-6578EDF7C006 |     10389 |        0 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |      9368 |        1 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |      9537 |        2 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |     18499 |        3 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |     15759 |        4 |
AD7D5099-A14D-48D4-9860-6578EDF7C006 |      5872 |        5 |

… and execute:

DELETE TableA_X_TableB
WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006'
AND TableB_PK = 9537

… I would like the Sequence values to read 0 1 2 3 4, and not 0 1 3 4 5.

I've tried numerous approaches to this. I won't list them all, but as an example of something that seemed most likely after a lot of failed experiments (and which also fails, to be clear!):

DECLARE @seq INT
SET @seq = -1;
WITH listEntries AS  (
SELECT TOP 100 PERCENT *
FROM TableA_X_TableB
WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006'
ORDER BY Sequence ASC)
UPDATE listEntries
SET @seq = listEntries.Sequence = @seq + 1
FROM listEntries;

This puts in an updated, numerically correct list of Sequence numbers, alright… but based on the internal database order, not the sort on the previous sequence number. The upshot is that users' carefully sequenced lists become jumbled the moment an item is eliminated.

I can think of a couple of workarounds for this, but

  • it strikes me that there ought to be a reasonably elegant way of doing this within T-SQL;
  • this issue has come up before, and is likely to come up again. I'd rather solve it the 'right' way rather than continue to apply the same elaborate hacks.

Thanks!

UPDATE =======================================================

A piece of the puzzle was that this table also had a couple of indexes; one to ensure that any combination of TableA_PK and TableB_PK was unique, and a couple of others to speed up some joins across a dataset of significant size. Without the indexes, my sample code above worked fine, but my naive ORDER BY clause would always be overridden by the them once they were in place. (There might have been a solution here, but the accepted one is far more elegant.)

Also, as I suspected, there are certain edge cases in the overall requirement in which sequence gaps are permissible — but only if, and only where, the end user says so. There's no way that I can see in which a straight-ahead ROW_NUMBER()-based solution could resolve that; @jpw's clever and concise 'recipe' below not only solves the original problem, but with a bit of tweaking also permits local resequencing where that becomes necessary.

RiqueW
  • 212
  • 1
  • 12
  • Something like this? -->http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table/8608085#8608085 – xQbert Sep 29 '14 at 17:51
  • 6
    Why are you bothering to store the sequence number if it has absolutely no bearing on the actual value in that row? Just determine the number at runtime (e.g. using `ROW_NUMBER()`) instead of trying to maintain this meaningless data in the database. It's really not meaningful at all until someone actually queries the data that actually exists at the time they run the query, right? So why break your back trying to keep the *stored* data in some magical, gapless nirvana? – Aaron Bertrand Sep 29 '14 at 17:54
  • Also you should search and read about "quirky update" - it is not a good idea IMHO. – Aaron Bertrand Sep 29 '14 at 17:58
  • quirky update certainly has two camps of people. There are some extremely stringent requirements before you can even attempt it. That alone is enough reason for many people to shy away from it. With the addition of LEAD and LAG it really seems to be not as needed as it used to be. – Sean Lange Sep 29 '14 at 18:21
  • @AaronBertrand: ROW_NUMBER() et al give me the sequence of items in the db, whether 'natural' order or based on some ORDER BY — I should have emphasized my statement more, that these "need to be ordered lists"… user-ordered in fact. The 'Sequence' column isn't meaningless data, it's the order in which a user has previously dragged the items in a rich client-side application. – RiqueW Sep 29 '14 at 18:48
  • I've left out a lot of context here that explains the "why", and boiled it down to the core of the problem for simplicity's sake. Because of the requirements of the client-side app, there are times when it's appropriate for the entire edited list to be fired back with ID's and sequence numbers in place, and times when we just need a laser beam removing a single item (I've shown an example with six items; the actual lists can run into the 100's.) The 'gaplessness' is also very useful in getting the back-end to play nicely with the front. – RiqueW Sep 29 '14 at 18:49
  • You can still use ROW_NUMBER() over that column without updating that column until the user re-drags. – Aaron Bertrand Sep 29 '14 at 19:00

1 Answers1

1

Maybe using row_number() partitioned over TableA_PK ordered by sequence like this would work:

UPDATE Table1 SET Sequence = rn
FROM Table1 
INNER JOIN (
    SELECT 
       [TableA_PK], 
       [TableB_PK] , 
       rn= ROW_NUMBER() OVER (PARTITION BY tablea_pk ORDER BY tablea_pk, sequence) -1 
    FROM Table1 
    WHERE TableA_PK = 'AD7D5099-A14D-48D4-9860-6578EDF7C006' 
) derived ON table1.TableA_PK = derived.TableA_PK and Table1.TableB_PK = derived.TableB_PK

Sample SQL Fiddle showing before and after delete and update

jpw
  • 44,361
  • 6
  • 66
  • 86
  • 2
    But why update the table? You're going to have to perform this maintenance ***every time*** any row is touched. Put the `ROW_NUMBER()` expression in a view, then you can get the real-time sequence at runtime without having to do all this extra maintenance for every single write, or even wasting space in the first place on a column that is a moving target. – Aaron Bertrand Sep 29 '14 at 17:59
  • @AaronBertrand I agree, that would be a better solution, but maybe there are reasons beyond my understanding why the OP wants to do this, even though other options would be better. I just showed a possible way (that I think should work). – jpw Sep 29 '14 at 18:01
  • 1
    @jpw Thanks for the effort in putting this together, and for the respectful understanding that code always fits into a larger context, and that we often find ourselves on the site looking not for the 'right' answer, but a way out of a corner we've been painted into. This solution is spot-on, elegant, concise, and (see my "Update" above) also gives me at least one potential method to resolve an ugly edge case. Great work!!! – RiqueW Sep 29 '14 at 20:00
  • @RiqueW Happy to have helped :) – jpw Sep 29 '14 at 20:02