Which index will always go in order (after deleting rows)?

Question

SQL Server

I have recently been working with a database, I don’t really understand indexes, now I have an IDENTITY (1,1) index in my table. Why, after deleting rows, the id of these rows are remembered and after that they are not repeated when new records are added, the countdown goes after the last deleted number. What index should be set so that this does not happen?

Answer 1

Commonly - identity is "base, provide uniqueness, I don't care how." That is, bothering about specific id values ​​in this case is not only undesirable, but also impossible.
Well, in general, the situation with deleting the last or last records is quite exotic. Most likely, the removal will be "from the middle."
If we are talking about clearing the table completely, then it is better to use truncate, which at the same time will reset the identity

Answer 2

after do not repeat when new records are added

This is the normal behavior of IDENTITY(1,1). You can change the receipt of the ID. Depends on the subject area. It is better to use a separate field "Number". There are a lot of options for obtaining a continuous sequence: for example, remember remote numbers or calculate them. In some cases, for example, an invoice - numbers in chronological order within a period, it is enough to use a range of numbers.

Answer 3

identity is just a counter. This property does not restrict integrity (key) and does not affect query performance (index).