I have an application that processes files and transform them to bcp files that are imported into a SQL server database using bulk insert. The application is using parallel threading, and as result multiple bulk insert queries are running at the same time. I'm using identity field with auto increment.
I need to know how SQL server is managing the increment Id in this case. Is the Id value allocated directly upon insert into the table ?
In case multiple bulk inserts are running at the same time, is it possible that some records from second file can have Ids in between of first file?
let's say I have 2 files A and B: A contains 6 records, while B contains 4 records. Here is how data is imported into the table X:
Id(auto increment) File
--------------------------
1 A
2 A
3 A
4 B
5 B
6 B
7 B
8 A
9 A
10 A
As you can see, records from B are written in between the records from A. My question here is is it possible that these B records could be written in database after all A records, although they have Ids in between ?
I'm asking this because I have another application that read from table X based on Id, similar to this query:
select top 10000 * from table X where Id >= last_id
order by Id
(Max Id from this query is then saved into last_id for next run)
In some cases, there are some records that are missing in second application, and that are similar to the case of A and B files. So what I'm suspecting is that at the time the query was ran in second application, the records of B records were not written yet in the table X, and as result were missed in this query, and also in the next run as their Ids are behind last_id
