Change an Existing Column to Computed and Persisted without dropping the table/column

I wanted to change an Existing Column in SQL Server to computed and persisted without dropping the table/column.

I have an auto-incrementing ID and another column ReportID which is formatted with this computation:

ReportID = 'RPT'+{FiscalYear}+{0s}+{Auto Incremented ID}

For example:

for ID = 1, 2, ...., 10, 11, ..., 100, 101
ReportID would be RPT2122000001, RPT2122000002,..., RPT2122000010, RPT2122000011, ..., RPT2122000101, RPT2122000102

Previously, we were doing this in an "after insert" trigger - compute the value and update the row. But by doing this some of the ReportIDs are getting duplicated when the load is high and the Reports are generated by different users in parallel.

So, to solve this I thought to change the existing column to a computed column with the 'RPT'+{FiscalYear}+{0s}+{Auto Incremented ID} but the problem is that I want the existing data to be remained the same. Because if the computation runs now, all the previous year's data will be modified with current financial year which is wrong.

When I try directly by setting the computed value in Management Studio, it is also internally running the drop and add back in the background.

I saw plenty of answers but they were not satisfying enough.

I tried to create a new column with the computed value and then try to rename, which also it is not allowing.

Edit 1

Error:

Computed column 'ReportID' in table 'Tmp_wp_tra_report_creation' cannot be persisted because the column is non-deterministic

Edit 2

Financial Year Calculation:

(select (case when ((select top 1 a.Pc_FromDate from wp_pc_calendar a where a.Pc_FromDate>=getdate())<=getdate()) then (select top 1 b.Pc_FinYear from wp_pc_calendar b where b.Pc_FromDate>=getdate() order by b.Pc_FromDate asc ) else (case when ((select top 1 c.Pc_FromDate from wp_pc_calendar c where c.Pc_FromDate<=getdate() )<=getdate()) then (select top 1 d.Pc_FinYear from wp_pc_calendar d where d.Pc_FromDate<=getdate() order by d.Pc_FromDate desc ) else 'No Records' end) end) as finyear)

Also, Is there a way without creating a new Column?

Solution

I would think to approach this by:

Renaming the original column
Creating the new computed column that uses the original column and fails over to a computation if the original column has no value (is null).

Like:

-- === Setting up some data for testing
drop table if exists test_reports
create table test_reports (
  id int identity(1, 1),
  fiscal_year int,
  report_id varchar(15)
)
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000001')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000002')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000010')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000011')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000101')
insert test_reports (fiscal_year, report_id) values (2122, 'RPT2122000102')

-- === Rename the existing column to something else because it will start to 
-- === contain nulls and the nulls may break existing code.
exec sp_rename 'test_reports.report_id', 'original_report_id'

-- === Add our new, computed column that checks the original column
-- === for a value and uses the original value, if available.  Otherwise,
-- === the computed column is an actual computation.
alter table test_reports add report_id as (
    coalesce(original_report_id,
             'RPT' + convert(varchar, fiscal_year) + right('000000' + convert(varchar, id), 6)))


insert test_reports(fiscal_year) values (2123)

select * from test_reports