1

I’m trying to figure out, if any row in my local database (Service-Based Database) is removed, I want give to second row (next after deleted) index of deleted line with same step back for all subsequent rows of course, also important to keep original order of lines as it was inserted. So same question I’ve start here: How to setup auto increment for Service-Based Database but now I'm looking for a different way of solving. I want this number of row for some calculations to read line. It soon became clear that it is better not to do it this way as I’ve tried it here Reset auto increment so seems I need numbers sequence. So in this case index numbers means sequence number, for example:

  1. line 1
  2. line 2
  3. line 3
  4. line 4

If line2 is deleted I want this result:

  1. line 1
  2. line 3
  3. line 4

If all lines deleted, and then inserted new line becomes 1. new first line and not 5. new first line as it works with index now.

Community
  • 1
  • 1
  • 1
    I would doubt you really need to store continuous numbers. Instead I would suggest using an auto increment field to maintain your order and then handling this "display" logic in a view... Don't think of the database as a presentation logic, but rather storage, where some fields can be ordered by to produce your expected results... – sgeddes Aug 26 '16 at 04:03
  • @sgeddes I've tried figure out with t-sql auto-increment setup to do this job form me here http://stackoverflow.com/questions/39157062/reset-auto-increment?noredirect=1&lq=1 but seems it is not very good idea –  Aug 26 '16 at 11:43

1 Answers1

0

I think you should use an auto-incremented field (say ID) to "keep original order of lines as it was inserted". And use a VIEW for example to select rows from a table using ROW_NUMBER() function to emulate sequence number

CREATE VIEW T_WITH_ROW_ID  
AS
SELECT ROW_NUMBER() OVER(ORDER BY ID) as ROW_ID,
       T.*
       FROM T

and use it as

SELECT * FROM T_WITH_ROW_ID
valex
  • 23,966
  • 7
  • 43
  • 60