I have a C# console app which is using a stored procedure to insert rows into a SQL Server database. Currently the database has about 1m rows, however the ID column is up at 26m!
On the last successful insert the ID went from 16m to 26m. The app runs multiple threads in parallel so there is a chance it could be trying to insert two things at the same time.
Is there anything other than a transaction rollback that would cause the identity column to increment without adding a row?
It seems unlikely that there were 10 million times that two threads tried to write a conflicting row at the same time.
I'm using a temporary table variable which is populated by passing XML into the stored proc:
-- insert data if it doesn-t already exist
insert into DataStore (DataText, DataSourceID)
select distinct td.dataText, td.dataSource
from @tempData td
where not exists ( select 'x'
from DataStore ds
where ds.DataText = td.dataText );
