Auto Increment Sql Value

In the infinte wisdom of the global DBA at a firm I am working at right now he has created a table that takes an int as an ID field, but does not auto increment the number. I am pa

Solution 1:

Well, I would start by going to your DBA and ask why he decided to not make the ID column and identity. Perhaps he will change his mind.

If, however, he will keep this decision, do not attempt to create an auto-increment mechanism on your own. 99.9% of the cases it has a potential to fail, especially in a multi user environment. Instead, use the already built in, thread safe method of an identity column.

Since we are talking about a situation where you can't use an identity column directly in your target table, I would suggest using a simple mimic of the sequence object introduced in 2012 version to get you your auto increment.

For this, you'll need a tally (numbers) table. If your DBA did not already create one, send him to to read Jeff Moden's The "Numbers" or "Tally" Table: What it is and how it replaces a loop and then send him to KM.'s answer on this SO post for the creation script. (Method 7 is my favorite.)

Now that you have a numbers table, you add a very simple table:

CREATETABLE tblSequence
(
    Valueintidentity(1,1)
)

Then, you create a stored procedure that will insert any number of rows into this table and returns the newly created values (Thanks to Martin Smith for the merge trick on this post!):

CREATEPROCEDURE stp_GetNextValues
(
    @NumberOfValuesasint
)
ASMERGEINTO Sequence
    USING (SELECT Number
           FROM   Tally
           WHERE  Number <=@NumberOfValues) T
    ON1=0WHENNOT MATCHED THENINSERTDEFAULTVALUES
    OUTPUT INSERTED.Value; 

GO

Then whenever you execute this stored procedure you'll get safe auto incremented values.

EXEC stp_GetNextValues 125

You can see the full script in action on rextester.

I leave it up to you to incorporate this into your own procedure.