How to calculate the (custom id) for a row which has not been inserted yet?

Question

I am using the answer of this question How to automatically generate unique id in sql server to create a custom id for a table.It worked perfectly.
Now I have a column which holds the values such as UID00000001 UID00000002 and so on. Suppose the last value in this column is UID00000003.Now I want to calculate the value for the row which hasn't been inserted yet via C# in one of my .aspx pages.In this case UID00000004. How can I achieve this value? Any help would be appreciated. Thank you.

Answer 1

If you are not required to generate these identifier at database level (e.g. some other processes insert records there), you can pre-generate them within your application. Something like above:

class Generator
{
     public static int UniqueId = 0;

     public static int GetNextId()
     {
          return Interlocked.Increment(ref UniqueId);
     }
}

Then, your code can preallocate these identifiers and also format those strings. If multiple users access the same functionality, they will receive other identifiers. However, if one does not (successfully) performs a save operation, those identifiers will be lost.

Answer 2

You need to execute this query to get the next identity which will be generated for the table:

SELECT IDENT_CURRENT('table_name')+1; 

For your case, it will have some other info concatenated with the next identity so the query will be like this:

SELECT 'UID' + RIGHT('00000000' + CAST(IDENT_CURRENT('table_name')+1 AS VARCHAR(8)), 8)

Of course you will need to write the C# code to send that query to the SQL Server.

Having said that, keep this in mind: When you get the value from that call and hold onto it, if during the time you are holding the value a record is inserted into that table, then the value is no longer the next value.

If you need the identiy value after a record is inserted in your application, please refer this answer.