Reordering Autoincrement in SQL

Asked

Viewed 56 times

-2

inserir a descrição da imagem aquiEae guys, like I created an ID table with Auto Increment, checking the table ví Bugou, have any SQL commands that reorder from 1,2,3,4 to last? just more for the sake of reorganization. Thank you.

c# sql sql-server auto-increment

  • Do other tables depend on these records? can it be recreated?... If you want to keep this order "clean" and manipulate your records, you should not leave Identity on

  • 3

    Understand that a primary key field with auto increment is not a field that needs to be organized in the eyes, understand it as a unique value in your table, which is a guarantee of a single record for the bank, so tampering with these values is not a good idea. And the bank does not "reorder", you can change the seed of the increment and force any value, but again, better leave it to the bank, worry about the "value" fields for the business, such as descriptions, dates, etc

  • but if you still want to "touch" the seed, see the command dbcc checkident

  • @Leandroangelo No table depends on this record, but the ID is the field of "Reference in Identification in the dead file, in possible queries or identification in the environment, in case of recreating, in case the data that are already inserted there can be no data loss?

  • @Ricardopunctual this command I checked it, will complete the spaces that became empty between the correct medium explain?

  • It will not complete, it changes the next value of Identity. If for example you want to manage from 1 to 177, you need to return it to 1, until it reaches 177, then change again (very bad), or create a new table, with correct Identity, starting from 1 and insert the values of this table there, leaving everything "in order"Then you change the names of the tables. Need to delete foreign keys to do this

  • 1

    Or write the data in a temporary, gives a truncate in the table and re-inserts this data from the temporary... But as @Ricardopunctual commented, you shouldn’t be worrying about the aesthetics but about the integrity of your data

  • Yes, the idea of @Leandroangelo is good, and do not forget to after deleting the records, change the seed of Identity to 1

  • Thank you guys, it’s been very helpful now the information you’ve given me. Thank you.

  • @Ricardopunctual truncate will already restart Identity Seed

  • 1

    interesting @Leandroangelo, did not know this, another one for knowledge box ;)

Show 6 more comments

1 answer

1

As discussed in the comments, you should not worry about this aesthetic issue of the identifier but rather about the integrity of the data.

In the scenario where this Primary Key is not Foreign Key of no other or even the same value for another weak benchmark. You can store the current table content, run a truncate and repopulate it again.

/* Criando a estrutura de armazenamento sem o ID */
DECLARE @TabelaTemporaria TABLE(
    Status VARCHAR(50)
    /* restante das suas colunas */
) 

/* Armazenando o conteúdo existente na [SUA_TABELA] para a @TabelaTemporaria */
INSERT INTO @TabelaTemporaria ([Status] /* restante das suas colunas */)
(SELECT Status /* demais campos sem o Id */FROM [SUA_TABELA])

/* Limpando a [SUA_TABELA] */
TRUNCATE TABLE [SUA_TABELA]

/* Populando novamente a tabela original */
INSERT INTO [SUA_TABELA] (idSistema)
(SELECT * FROM @TabelaTemporaria)

Browser other questions tagged c# sql sql-server auto-increment

You are not signed in. Login or sign up in order to post.