CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Gaurav
Gaurav

Reputation: 915

Update identity column to maintain unique number

We have a table to maintain the next available unique number for a specific use-case in our application. We thought of having a column of type bigint which will be incremented and provide the next available number. Is it possible to make this column as identity column and auto-increment the same row?

I found a link on stackoverflow Usage of Table with single column (Identity)? with similar requirements. However the solution mentioned would insert a new record in the table. Instead of insert, i would like to do an update on same record to keep track of next available unique number.

Is it possible to leverage Identity column feature or I would need to fetch the current value, explicitly increment and store it back into the same row in same column?

Upvotes: 3

Views: 383

Answers (2)

bendataclear
bendataclear

Reputation: 3850

I'd create a Stored Procedure to start a transaction, increment the stored bigint (not an identity) and return the new value, rolling back the transaction if anything goes wrong.

The update section of the SP would be like (can't test atm):

DECLARE @RET TABLE (Value BigInt)

UPDATE Mytable
SET MyField += 1
OUTPUT inserted.MyField INTO @RET

IF (SELECT MAX(Value) FROM @RET) > 0
RETURN (SELECT MAX(Value) FROM @RET)

OUTPUT Statement

This would make only 1 write to the table and return the new value. Checking the value will return the last value issued.

Upvotes: 1

Philiop
Philiop

Reputation: 473

Not entirely sure what you are trying to achieve. Believe your just after an identy adding i.e.

CREATE TABLE #Temp (rowName BIGINT IDENTITY(1,1) NOT NULL)

This would create a table with a column called rowName which auto increments by one.

Upvotes: 2

Related Questions