Delete and relocate index

Question

How should I associate data to have a db SQL Server 2017 with a good performance. I am doing a data association and being new in this area I feel a little lost.

I have the following structure as an example:

Let’s say the structures above have the following data:

Delete and relocate

Using the example above I look for a solution to for example, if the "U2" on the table Account is excluded, the u3 be relocated to index 2 and the same change happens in the table Account_Data.

Doubts

This type of action is possible with pure SQL?

Should I use another language to manage these events?

Answer 1

I’m finding it odd to have these two tables separated, but since there’s no description of the case, you might actually need it. By the name and structure of the tables something tells me you don’t need.

If you want an action in a table to trigger action in another table you need to use triggers. But if there’s only one table You don’t even need this :)

The primary key should never be changed in the database. It doesn’t matter that there will be holes. The only exception is if you create a routine that modifies all references to it throughout the database and can ensure that these keys were not used anywhere outside of DB. But I wouldn’t do that. Practically zero advantages and there are risks.

What you call index is this key, index is a set of keys.

Trying to do what you want will not make the database faster, this is micro-optimization and those that do not bring gains. There’s a chance it could get worse.

To tell the truth in most scenarios I know nothing should be effectively erased unless one has a strategy of how to do this properly.

Even if I wanted to do it, I don’t see the link between one table and another. If it’s the id then you really don’t need two tables and then the description of the question is wrong.

Model right, create the right indexes, make the right queries, configure the server correctly, make the application the right way and all the architecture of the solution is adequate, make tests, analyze the use, see the bottlenecks, all this is what will make it perform.

Answer 2

Responding to your comment in Maniero’s reply:

Suppose you have multiple records:

n
1
2
3
4
5
6

You will delete n 3. After deletion, simply perform a command:

update tabela set n = n-1 where n > 3;

But, also as Marisol said, I don’t see anything that that solves, and something totally unnecessary.

If you want the line number to come sequentially in a select without jumping, there are functions that do this.