Assign a range of invoice numbers to each person who adds new invoices.

You could implement this scheme by creating a table with the columns user name and invoice number. The table would have one row for each user that adds invoices. Each time a user adds an invoice, the number in the table would be incremented and used for the new invoice. To handle all tables in the database, the table should have three columns: table name, user name, and last key value. You should periodically verify that each person has enough numbers.

Create a table with the columns table name and last key value.

One row in the table contains the last invoice number used. The invoice number is automatically incremented every time a user adds an invoice, establishes a new connection, increments the invoice number, or immediately commits a change. Other users can access new invoice numbers because the row is instantly updated by a separate transaction.

Use a column with a default value of NEWID in conjunction with the UNIQUEIDENTIFIER binary data type to generate a universally unique identifier.

You can use UUID and GUID values to uniquely identify table rows. Because the values generated on one computer do not match the values generated on another computer, the UUID and GUID values can be used as keys in replication and synchronization environments.

For more information about generating unique identifiers, see The NEWID default.

Use a column with a default value of AUTOINCREMENT. For example:

CREATE TABLE Orders (
   OrderID INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   OrderDate DATE,
   primary key( OrderID )
);

On inserts into the table, if a value is not specified for the autoincrement column, a unique value is generated. If a value is specified, it will be used. If the value is larger than the current maximum value for the column, that value will be used as a starting point for subsequent inserts. The value of the most recently inserted row in an autoincrement column is available as the global variable @@identity.