I was curious what would happen with inserting a values into a row with an identity values and then rolling back the transaction. Would the identity value created in the rolled back transaction be discarded?
Here’s a script to create a table with an identity column, with a few inserts and a rollback.

CREATE TABLE #IdentityTest(RecordID int not null identity(1,1), DisplayName varchar(20) not null);

INSERT INTO #IdentityTest(DisplayName) VALUES ('Record1');
SELECT SCOPE_IDENTITY();

BEGIN TRANSACTION;
INSERT INTO #IdentityTest(DisplayName) VALUES ('Record2');
SELECT SCOPE_IDENTITY();
ROLLBACK;

BEGIN TRANSACTION;
INSERT INTO #IdentityTest(DisplayName) VALUES ('Record3');
SELECT SCOPE_IDENTITY();
COMMIT;

SELECT * FROM #IdentityTest;

We can see that ID value 2 is generated for the 2nd insert, the transaction is rolled back, and the ID value 3 is used for the next value. So this will leave a gap in the identity values, the engine won’t try to fill the gap. This makes sense, especially in an environment where many processes would be hitting this table at the same time.