SQL Index Unico

Asked

Viewed 21 times

2

I have a question on SQL Server. I have the following table, ID (integer Key), Name (nvachar), CPF (nvachar, with index unico).

If I insert with the CPF that already exists, SQL returns with error and this is OK. Now if I do the Insert and it succeeds, what I noticed is that the ID column jumps and it’s not in sequence.

|   ID   |    Nome    |    CPF    |
|   1    |    João    |    1234   |
|   2    |    Pedro   |    1235   |
|   3    |    Judas   |    1236   |
|   5    |    João A  |    12347  |

From what I understood, when I tried to insert and gave error, SQL itself inserted and then deleted the record, so the next ID is 5 and not 4. Is there any way that this does not occur? Or the best way to treat it is to check before inserting?

Thank you

sql-server índices

  • Assuming that your field id whether auto-increment type so this is the expected behavior, there may be "holes" in the sequence.

  • If you need to keep order. It is interesting to check in the application. Validate whether or not the user can be inserted. Although the database does not allow.

1 answer

2

Your ID field should be IDENTITY.
Each time an operation will be done, the "seed" that generates the next value, provides a value before performing the operation, after all, needs to have it to do (or try) the INSERT, and for reasons of competition (another transaction may "ask" for the next ID before ending the INSERT which obtained the previous ID), once provided an ID, this is considered used, and if error occurs it is lost.

From the MSDN document:

Reuse of values - for a specific seed/increment identity property, the identity values are not reused by the mechanism. If an insertion instruction or if the insertion instruction is reversed, the Identity values consumed will be lost and will not be generated again. This can result in intervals when the values of subsequent identity are generated.

Source: https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-table-transact-sql-identity-property

Browser other questions tagged sql-server índices

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