Generating a unique batch id (SQL Server)

Question

This is possible 2x questions in 1x. Sorry about that, but here goes:

PROBLEM

I am creating a unique batch id everytime a user uploads some data to SQL Server. Currently, I do this by looking at the last value of the 'Identity Specification' and add +1 to that.

Problem arises, as you might have guessed, if multiple users input data at the same, they both would get the same batch id...

Possible Solution

In order to mitigate this issue, I have come up with this method to generate 3 letter + random number; and the (last id value + 1):

DECLARE @tmp CHAR(3) = CHAR(CAST(RAND()*26 AS int)+65) + CHAR(CAST(RAND()*26 AS int)+65) + CHAR(CAST(RAND()*26 AS int)+65);
SELECT @tmp;

select cast(RAND()*9999 as int)

(1) I am not sure how to concatenate this into one line of string.

(2) The other question, is there a way to 100% guarantee every user is given a unique batch id every time they submit a request, regardless of how many are doing it simultaneously?

I would really appreciate your input in this.

Answer 1

1 - Concatenation part is very simple, you can do the following:

DECLARE @tmp VARCHAR(10); 

SET @tmp = CHAR(CAST(RAND()*26 AS int)+65) 
         + CHAR(CAST(RAND()*26 AS int)+65) 
         + CHAR(CAST(RAND()*26 AS int)+65)
         + CAST(cast(RAND()*9999 as int) AS VARCHAR(4));

SELECT @tmp;

2 - I would suggest to populate a table with the Random values you would like to issue to users and then select from it, to avoid the race-condition.

Create a table called BatchNumbers with two Columns BatchNumber and Used.

Populate the batch number table and 0 as default value for Used Column.

Then everytime you need a batch number do the following.

CREATE PROC dbo.usp_Get_BatchNumber
  @BatchNumber VARCHAR(10) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

Declare @t TABLE (BN VARCHAR(10));

    UPDATE TOP (1) BatchNumbers
     SET Used = 1 
    OUTPUT inserted.BatchNumber INTO @t (BN )
    WHERE Used = 0; 

    SELECT @BatchNumber = BN FROM @t;

END