This question has already been answered:

Suppose I have a base:

|ID | Name | Count| | 1 | val1 | 3 | ...... | 7 | val7 | 3 | | 8 | val8 | 3 | | 9 | val9 | 3 |

If I delete the last item (9) and add another item, then the ID will be 10.
How do I determine in advance which ID will be assigned to the next product?

Reported as a duplicate by members Bald , aleksandr barakin , user194374, Grundy , VenZell Jul 20 '16 at 9:42 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

  • base specify, mysql / sqlite / postgres / mssql / firebird, etc. - strangeqargo 8:17 pm
  • Microsoft SQL server - Laziz Ergashev

1 answer 1

 SELECT IDENT_CURRENT ('Моя таблица') + 1; GO

The function returns the last value returned for the IDENTITY field, adding one — get the next value.

If you are going to do something with it later, I do not recommend using it in this way, it is better to use one of the SCOPE_IDENTITY or @@ IDENTITY functions after insertion, if you can guarantee that one line was inserted, or use the OUTPUT expression to put all inserted values ​​into a temporary table and subsequently process them.

  • you can think so, until in the table from which nothing was deleted you see the sequences 1,2,3, 1001 . in one session this will not happen, but in general it is not. - teran
  • @teran, judging by the classic 1000 break, you seem to be faced with caching IDENTITY values. Starting, it seems, with the version of SQL Server 2012, the new IDENTITY values ​​are cached in memory of 1000 pieces each, and are issued to new records from memory. If the server reboots, the cache disappears, and SQL Server caches the next 1000 entries, and starts issuing identifiers from them. In general, IDENTITY does not guarantee continuity, it guarantees only non-repeatability (there will be no duplicate values) and a monotone increase (the next value returned will be greater than the previous one). - minamoto
  • so here about that and speech, that +1 as a whole - not the answer. - teran
  • @teran, that is why I made a reservation that you should not rely on such a value, and you need to use other methods. - minamoto