1

I'm wondering if SQL Server (i.e. the T-SQL language) has a natural way of doing this or if I have to write fancy constraints/triggers.

Suppose I have a table

       RebuplicanCandidates
===================================
 Id |      Name       | ByteIndex
===================================
 1  | 'Marco Rubio'   |    0
 2  | 'Jeb Bush'      |    1
 3  | 'Donald Trump'  |    2
 4  | 'Ted Cruz'      |    3

and I remove JebBush: 

DELETE FROM [RepublicanCandidates] WHERE [Id]=2

Then I want the table to be like 

       RebuplicanCandidates
===================================
 Id |      Name       | ByteIndex
===================================
 1  | 'Marco Rubio'   |    0
 3  | 'Donald Trump'  |    1
 4  | 'Ted Cruz'      |    2

Notice that the ByteIndex column shuffled.

And then if I insert a candidate

INSERT INTO [RepublicanCandidates] (Name) VALUES ('CarlyFiorina')

the table becomes 

       RebuplicanCandidates
===================================
 Id |      Name       | ByteIndex
===================================
 1  | 'Marco Rubio'   |    0
 3  | 'Donald Trump'  |    1
 4  | 'Ted Cruz'      |    2
 5  | 'Carly Fiorina' |    3
Micro Optimizer
  • 227
  • 1
  • 6
  • 1
    ehm....what happened with the inserted row? – Lamak Nov 04 '16 at 16:57
  • @Lamak Oops, there you go – Micro Optimizer Nov 04 '16 at 17:01
  • 1
    Why do you have to store a ByteIndex with no gaps? Deleting the first row would mean that all other rows in the table have to be updated. – Martin Smith Nov 04 '16 at 17:04
  • 1
    If you created a VIEW based on the table, you could add a row_number() function instead of the ByteIndex column. – VDK Nov 04 '16 at 17:04
  • 1
    I have to wonder what value there is to having an _unstable_ numbering of `RebuplicanCandidates` _[sic]_ in the database might be? It might be useful for an application to display candidates with dense (?!) numbers assigned, but that is a matter for the application. You couldn't go back and determine who was "14" (and whether they would sell you a pizza) without keeping a complete history of the wandering values and knowing _when_ the "14" was of interest. – HABO Nov 04 '16 at 20:23
  • Possible duplicate of [Using a sort order column in a database table](http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table) – philipxy Nov 05 '16 at 00:07

2 Answers2

4

If you created a VIEW based on the table, you could add a row_number() function, and drop the ByteIndex column from the base table.

CREATE VIEW vRebuplicanCandidates
AS

SELECT id, name , ROW_NUMBER() OVER (ORDER BY id) - 1 AS ByteIndex
FROM RebuplicanCandidates
VDK
  • 573
  • 3
  • 8
0

T-SQL cannot do what your are asking. You will have to write some code or the suggestion in the other answer by is a good one IMHO.

Neo
  • 3,309
  • 7
  • 35
  • 44