Deleting rows, reseeding Non PK Index Column? C#

Question

I have been racking my brain and searching the web on how to do this, but no luck.

The problem,

I have a datagrid which allows the user to add or delete rows as they please, it is indexed via an Index column which is NOT a PK column in the Databse, eg: ID is the PK column in this case,

  ID    |   Name   |  Index
Guid 1  | Name 1   |    1
Guid 2  | Name 2   |    2
Guid 3  | Name 3   |    3
Guid 4  | Name 4   |    4
Guid 5  | Name 5   |    5

So when as using deletes a row this happens: In this example Row 2,

  ID    |   Name   |  Index
Guid 1  | Name 1   |    1
Guid 3  | Name 3   |    3
Guid 4  | Name 4   |    4
Guid 5  | Name 5   |    5

So how do I reset the Index keeping the current order when a row is deleted? So it looks like this,

  ID    |   Name   |  Index
Guid 1  | Name 1   |    1
Guid 3  | Name 3   |    2
Guid 4  | Name 4   |    3
Guid 5  | Name 5   |    4

I have looked at Reset identity seed after deleting records in SQL Server , but this is server side and I want to do this application side via C#, not to mention this seems to only work for PK columns?

How would I go about doing something like this?

Just imagine how bad it would be for perfomance. You delete row with index 1 and _all_ rows in the table have to be updated. — Evk, Mar 23 '17 at 11:40
If you really wont to do this you will have to do this serverside. Image that 2 or more c# clients are doing this at the same time... — GuidoG, Mar 23 '17 at 11:45
@Evk, Oh don't get me started, I was hoping there was an efficient solution. But, in this case there will be a max of maybe tens of rows , maybe in the hundreds which should not pose a problem. — KyloRen, Mar 23 '17 at 11:47
@GuidoG, Did not think about that, still curious as to if this is doable. Thanks — KyloRen, Mar 23 '17 at 11:48
And you are completely sure you need that column? Because there is ROW_NUMBER function in sql server for example, to assign sequence number to the query result. You can also filter by it (if you need something like paging). — Evk, Mar 23 '17 at 11:49
@Evk, I am absolutely unsure if I need this column. In my limited experience in Databese'ing, I am trying to find a way to keep an index of rows to keep them in order when using a`Guid` column as a PK . — KyloRen, Mar 23 '17 at 11:52

score 2 · Accepted Answer · answered Mar 23 '17 at 11:54

2

From what you are describing, you don't need the Index column in your table. You should however write a view on top of your table and use the row_number() function to achieve your goal, like so:

    create table [Example]
    (
        [ID] uniqueidentifier not null primary key,
        [Name] varchar(50) not null
    )
    go

    create view [vExample]
    as
        select [e].[ID]
             , [e].[Name]
             , row_number() over (order by [Name] asc) as [Index]
        from [Example] as [e]
    go

    insert into [Example] ( [ID] , [Name] )
    values  (newid(), 'Name 1')
          , (newid(), 'Name 2')
          , (newid(), 'Name 3')
          , (newid(), 'Name 4')
          , (newid(), 'Name 5')

    select * from [vExample] as [e]

    delete from [vExample] where [Index] = 2

    select * from [vExample] as [e]

But I don't recommend deleting records based on that column, rather use the actual primary key so that you are sure you are deleting the correct row. I've just used it like this as a demo. In the real world, another connection might have deleted "row 2" already, meaning that you will delete the following row when you use the same where clause because the numbering has changed with the first delete.

answered Mar 23 '17 at 11:54

Erwin Dockx

273
1
8

I don't intend deleting based on the index column, the index column is purely for keeping order of the rows. – KyloRen Mar 23 '17 at 11:57
Using a uniqueidentifier as primary key is not good for performance I do not recommend it. Indexing is always slower with this datatype – GuidoG Mar 23 '17 at 11:58
How do I set up `row_number()` column to suit my needs ? Cheers. – KyloRen Mar 23 '17 at 12:04
Well, if you only need that column to order the results, you can define it in a view, like I did in my example. I've added a link to the documentation of that function for full reference. Basically you specify an "order by" clause for the row_number() function which it uses to calculate the actual row number. But if you only need a visual order, you can achieve that with a stand-alone order by clause as well. – Erwin Dockx Mar 23 '17 at 12:37
@Guido I've been working much closer to home since 2010, no more traffic jams ;-) – Erwin Dockx Mar 23 '17 at 12:38
@ErwinDockx Same here, I am working about 20 km from home now. Nice to see you here – GuidoG Mar 23 '17 at 12:48
OK,I see what you mean now. The issue is that I want to keep the order for the life of the database, and the order may not necessarily be in the order the row has been added, hence the Index row to deal with this. – KyloRen Mar 23 '17 at 22:12

Deleting rows, reseeding Non PK Index Column? C#

1 Answers1