Do numerical primary keys of deleted records in a database get reused for future new records?

Question

For example if I have an auto-numbered field, I add new records without specifying this field and let DB engine to pick it for me.
So, will it pick the number of the deleted record? If yes, when?

// SQL Server, MySQL. //

Follow-up question: What happens when DB engine runs out of numbers to use for primary keys?

Answer 1

NO. numerical primary keys will not reused, except you specify them manually(you should really avoid this!)

Answer 2

AFAIK, this could happen in MySQL:

How AUTO_INCREMENT Handling Works in InnoDB:

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

After a restart of server. Innodb reuse previously generated auto_increment values. :

Suggested fix: innodb table should not lose the track of next number for auto_increment column after restart.

Answer 3

Depends on the auto-numbering system. If you're using a sequence of any kind, the numbers of deleted records will not get reused, as the sequence does not know about them.