Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not?
When does it generate the new ID? At transaction start or finish?
Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully?