Question about missing identifiers in an identity column in MSSQL

Question

Let's say that I have an MSSQL table with two columns: an int identifier column that contains an identifier column and another datetime column, or any other. Let's say the table contains 10 records with identifiers 1-10. Now I delete the record with ID = 5.

Are there any scenarios when another record "populates" this missing ID? That is, when the record is inserted and identifier 5 is set?

Answer 1

, ( ) 5. SQLServer , , , .

Answer 2

SET IDENTITY_INSERT, ID = 5

MS-SQL , .

Answer 3

, , "" IDENTITY, - IDENTITY. (SQL Server 2008):

CREATE TABLE Test 
(
   ID INTEGER IDENTITY(1, 1) NOT NULL, 
   data_col INTEGER NOT NULL
);

INSERT INTO Test (data_col) 
   VALUES (1), (2), (3), (4);

DELETE
  FROM Test 
 WHERE ID BETWEEN 2 AND 3;

DBCC CHECKIDENT ('Test', RESEED, 1)

INSERT INTO Test (data_col) 
   VALUES (5), (6), (7), (8);

SELECT T1.ID, T1.data_col 
  FROM Test AS T1
 ORDER 
    BY data_col;

:

ID  data_col
1   1
4   4
2   5
3   6
4   7
5   8

This shows that not only the “holes” are filled with new automatically generated values, values ​​that were automatically generated before being copied again, and may even duplicate existing IDENTITY values.