Persistent autoincrementing value, not attached to row insertion?

I need to get a unique value to use for a record *before* the record is added to a table. It doesn't have to be contiguous with existing records, but it must always be unique, has to be persistent over multiple instantiations of an ASP.NET application, and has to work in that sort of a multi-session environment (where other sessions could need additional unique values before the first session gets around to actually adding a record to the table).

I considered generating and using unique CLSIDs for this, but the resulting value also needs to become part of the filename of some files that are being saved to the disk (and those names also saved in the table), and including text CLSIDs along with other filename data would make for some unpleasantly long and difficult to work with filenames.

I also don't think there's a practical way for me to use a trigger associated with an identity column for this, because I need to save files to disk using the unique value before I even know if the record will in fact end up being added to the table, and what's more, the numbers and names of those files will vary in ways that might be difficult to handle in a stored procedure.

What I'm thinking I will have to do is create a separate database table called something like "UniqueIDGen". This table would have a single record in it with a single integer value, initialized to a value of 1. Then, each time an ID is needed, this one record would be locked, read and incremented by 1. The only reason for doing it this way instead of with an application variable, as I see it, is that the values need to be unique and continue incrementing in perpetuity, no matter how many times the ASP.NET application is recycled or the server is rebooted.

But I still have to wonder if there might be a more efficient method provided by SQL Server for this type of unique value generation ... something that is equally as persistent without requiring an entire table with only a single record to be allocated to such a basic task. Does anyone know of a more elegant solution for this?do a hash of the current date.time.milliseconds, or a unix timestamp in the backend of the asp.net, unless you plan on data being entered in the same second.|||Is there no better option based upon SQL Server or some other persistent technology, other than the workable but awkward solution I mentioned? For some reason I thought there might be a non-table-based identity value, or something like that, specifically for situations like this. Perhaps that is something I'm remembering from some other RDBMS software I dealt with in the past, and not SQL Server?|||Hi,

You could do this, it is a bit like Oracle's NextVal:

CREATE TABLE dbo.Sequences
(
ID int NOT NULL
)

INSERT INTO Sequences (ID) VALUES (0)

CREATE PROCEDURE dbo.NextValue
@.ID int output
AS
UPDATE Sequences SET ID = ID + 1, @.ID = ID + 1
RETURN

And call the stored proc to get the ID.

private void Button1_Click(object sender, System.EventArgs e)
{
string connectionString = @."Server=wpeude-masonix2;Database=TestDatabase;User ID=sa;Password=sa;Trusted_Connection=False";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "dbo.NextValue";
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@.ID", SqlDbType.Int, 4);
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);
connection.Open();
command.ExecuteNonQuery();
int pk = (int)param.Value;
connection.Close();
lblNextValue.Text = pk.ToString();
}

The ID would be unique in the Database. You could change the sproc to use NEWID() and have a varchar column instead, if you wanted to use a GUID.

A.|||asmason,

Your example is precisely the solution I proposed in my original post (unless I did an insufficient job of describing what I was thinking of). If there's no method available that's superior to creating a table for this purpose, then I'll go ahead and do it that way.

Thanks for taking the time to write it up.|||Couldn't you take and make a stored procedure that makes it for you? Basically when you go to insert a value you do a :

select @.IDVariable = Max(IDField) from Table

Then when you insert:

 Insert into Table (IDField) Values (@.IDVariable)

I have done something very similar to this in several projects and it remains unique and it is available when you create it.|||Regarding the suggestion of:

select @.IDVariable = Max(IDField) from Table

... and then ...

Insert into Table (IDField) Values (@.IDVariable)

I don't think I should do that. In my case, fair amount of time (seconds or even minutes) can potentially elapse between the time I need the unique ID (the first line) and the actual INSERT (the second line). What happens when, when session #1 is sitting at some point in between those two statements, and session #2 comes along and performs the first statement to get an ID for itself? It will get the same one that the first session got ... and then. if both sessions end up inserting a new row using the provided ID ... *biff*, collisions.

In fact, generally it seems like this wouldn't be wholly safe in any multi-threaded environment (even a delay of milliseconds could theoretically lead to a collision, though it might be much less likely than in my current project). Maybe it would be okay if both statements were encased within a single transaction, but that doesn't allow the ID value to be used for things outside of the database, which is a requirement of what I'm working on.

Also, I can only assume that the performance of the MAX function degrades as the number of rows in the table increases, so that may be a bit of a negative as well, at least compared to the standalone "ID table" method.

For what it is worth, I've already gone ahead and implemented a table for this task, very similar to the code that asmason included above, and it works very nicely. I did add one thing: a "counter_name" field, so that the same table could be used to maintain additional unique counters or values if I run across similiar needs again in the future. Thus, the statement in my stored procedure looks like this:

UPDATE AppCounters SET id = id + 1, @.RETURNID = id + 1 WHERE counter_name = @.ctrname