CODEWITHSUNDEEP
sql-server
AppeShopper
AppeShopper

Reputation: 265

SQL Server Computed Column that doesn't affect existing data

is that possible to do a computed column without affecting existing data?

I have a unique index column named 

"BookingNo"

For newly insert Column I want it to be following this format

(format([CreationTime],'yyMMddHHmmssff'))

I tried used Computed Column but it modified all my existing data.

my existing BookingNo format was 

201800123

Is there anyway to generate via database? Or we have insert via code?

Upvotes: 0

Views: 45

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82514

You could just add a default constraint:

ALTER TABLE TableName
    ADD CONSTRAINT DF_BookingNo DEFAULT(format(SYSDATETIME(),'yyMMddHHmmssff')) 
    FOR BookingNo

This way you will get the value only for newly created rows.

Please note that if this column has a unique constraint on it some inserts might fail if they are executed at the same time.

Upvotes: 2

Related Questions