CODEWITHSUNDEEP
sqlsql-server
Quantico
Quantico

Reputation: 2446

Adding a computed column that uses MAX

I need to create a sequential number column for record number proposes

I am OK with losing sequence if I delete a row from the middle of the table For example 1 2 3

If I delete 2, I am ok with new column been 4.

I tried to alter my table to 

alter table [dbo].[mytable]
add [record_seq] as (MAX(record_seq) + 1)

but I am getting An aggregate may not appear in a computed column expression or check constraint.

Which is a bit confusing? do I need to specify an initial value? is there a better way?

Upvotes: 0

Views: 772

Answers (2)

Dan Breidegam
Dan Breidegam

Reputation: 56

If you're looking to allocate a sequence number even in cases where the table doesn't get a record inserted, I would handle it in the process responsible for performing those inserts. Create another table, in this table keep track of the max identity value of that sequence. Each time you want to perform an insert, reserve the sequence number you want by updating that table first. If you rely on selecting the max existing value, you could be at risk of multiple sessions getting the same "new" sequence number before inserting. Even if the insert fails, you will have incremented that control table so nothing else uses that value that has been reserved.

Upvotes: 2

Afshin Rashidi
Afshin Rashidi

Reputation: 353

Its not supported in MsSql. You can use identity column:

ALTER TABLE  [dbo].[mytable]
ADD [record_seq] INT IDENTITY

Or use trigger to update your seq column after insert and/or delete

Upvotes: 1

Related Questions