This question already has answers here:
Create an auto incrementing alpha numeric primary key (1 answer)
Dynamic Default value while defining column definition (1 answer)
Closed 16 days ago.
Small reproducible query:
INSERT INTO records (serial_number, cust_name)
VALUES ('REGION_CODE' + IDENT_CURRENT('records'), 'CUST_NAME')
OUTPUT Inserted.serial_numberThe idea is to generate a serial number based on the autogenerated integer ID and REGION_CODE prefix stored separately which is different for each region which is looked up in the application code.
The problem is sometimes, there is a rare occurrence there are two queries which differ in seconds causing IDENT_CURRENT('records') to return the same ID. This causes two rows to have duplicated ID's. We have multiple transactions every second, but this duplicate ID incident only happens about once a month, so I am guessing there are some anomalies.
Is there a way to ensure that the serial_number generated will never be the same and relate to the current auto generated id?
We have tried using SCOPE_IDENTITY() , but it seems to return lagged id.
Triggers won't work since we need to return the inserted serial_number
1条答案
按热度按时间xxb16uws1#
Why don't you just make this a computed column inside SQL Server?
Then you don't need to insert values into it at all - it will automagically show the appropriate "constructed" ID for you.
I guessed that your primary key column is called
RecordsId- if it's something else, adapt as needed.This will concatenate together the contents of your
Region_Codeand yourRecordsIdcolumn and surface that as a new columnSerial_Numberin yourRecordstable.Any time you insert a value like this:
the primary key (auto-generated by the
IDENTITYspec) will be incremented, and theSerial_Numbercolumn will be automagically set to the expected value. No more need to fiddle with getting theIDvalue from theINSERTand inserting the value forSerial_Numberyourself - let SQL Server take care of that for you