My company uses software for creating bills for customers. The software accesses the SQL Server database frequently for storing and accessing bill data from the BILL table. The software is used by many of our colleagues in parallel.
The BILL table contains the following columns:
id (PK, int, not null)
nummer (int, not null) (bill number)
zeileErstelltAm (datetime, not null) (row created on)
The software has the following INSERT query for the BILL table.
INSERT INTO BILL (nummer)
VALUES (ISNULL(MAX(nummer) + 1, 1));
The idea is to get the last max number(nummer) from the BILL table then add one to the same number(nummer) and insert it as the new number(nummer) so basically trying to simulate unique number behavior.
Unfortunately, this query is causing problems as shown in the above image. The column number(nummer) is sometimes the same for many rows and the row creation date and time show a minor difference in milliseconds. I believe this problem is due to parallel access of the database by the software or some other reason which I don’t understand.
I tried lots of options like NOT EXISTS operator, Transactions and etc. still the problem occurs.
How can I make the column nummer unique without changing the database design or creating a new table from existing data?
