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

Question

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?

Answer 1

I would think to approach this by:

1. Renaming the original column
2. 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

Answer 2

In my example

Try this,

drop table if exists test_reports
create table test_reports (
  id int identity(1, 1),
  col1 varchar(10),
  report_id varchar(15)
)
insert test_reports (col1,report_id) values ('çol1', 'RPT2122000001')
insert test_reports (col1,report_id) values ('çol1', 'RPT2122000002')
insert test_reports (col1,report_id) values ('çol1', 'RPT2122000010')
insert test_reports (col1,report_id) values ('çol1', 'RPT2122000011')
insert test_reports (col1,report_id) values ('çol1', 'RPT2122000101')
insert test_reports (col1,report_id) values ('çol1', 'RPT2122000102')

Step 1

, Rename old column

sp_rename 'test_reports.report_id', 'Oldreport_id', 'COLUMN';

Step 2 : Get max auto incremented id column value.

Step 3 :

ALTER TABLE test_reports

    ADD report_id AS CASE
                         WHEN id > 6
                         THEN 'RPT' + CAST(YEAR(GETDATE()) AS VARCHAR) + CAST(id AS VARCHAR)
                         ELSE Oldreport_id
                     END;

Step 4 Try new insert

 insert test_reports (col1) values ('çol1')
    select * from test_reports

You can use your own logic for fiscal year, it will work.

Answer 3

since you are calculating fiscal year , you can put the fiscal year calculation inside a scalar function and use it in your computed column :

1. make function for fiscalyear calculation:

CREATE FUNCTION fiscalyear(@currentdate AS datetime)
RETURNS int 
AS BEGIN 
   @fiscalyear int = <yourlogic here >
 RETURN @fiscalyear
end

however in your fiscal year calculation , you should make it depends on an input param date (instead of getdate()) and in your computed column you pass something like reportdate or insertdate for that row.

because if your calculation is based on the getdate() , the value for computed column will be changed next year for the same row. and that is not what you want.

also using a function would be considered as non deterministic and computed column can't be persisted.

then follow up what other said as well:

2. rename old column :

exec sp_rename 'test_reports.report_id', 'original_report_id'

3. add another column to save the reportdate

alter table tablename
add reportdate datetime not null default (getdate())

you can set a default for it and no inserty/update needs to get modified

4. add the new computed column :

alter table test_reports add report_id as (
    coalesce(original_report_id,
             'RPT' + convert(varchar,dbo.fiscalyear(reportdatecolumn) + right('000000' + convert(varchar, id), 6)))

Answer 4

I faced a similar issue as of yours long time back and below solution worked for me.

You might be facing this issue because in case of bulk insert it will just update the value of the column (for all rows) with last updated value, and so you are getting duplicate values.

Considering your existing structure - after insert trigger, one of the approach would be to continue using the trigger and try iterating through the INSERTED table

IF (OBJECT_ID('tempdb..#T') IS NOT NULL)
BEGIN
    DROP TABLE #T
END

CREATE TABLE #T (
    [Serial] INT IDENTITY(1, 1)
    ,[ID] INT
    ,[ReportID] VARCHAR(100) -- data type you are using 
    )

INSERT INTO #T (
    [ID]
    ,[ReportID]
    )
SELECT [ID]
    ,[ReportID]
FROM INSERTED

DECLARE @LoopCounter INT
    ,@MaxId INT
    ,@ID INT
    ,@ReportID VARCHAR(100)

SELECT @LoopCounter = MIN([Serial])
    ,@MaxId = MAX([Serial])
FROM #T

WHILE (
        @LoopCounter IS NOT NULL
        AND @LoopCounter <= @MaxId
        )
BEGIN
    SELECT @ID = [ID]
        ,@ReportID = [ReportID]
    FROM #T
    WHERE [Serial] = @LoopCounter

    /*  write your logic here, you can use @ID to identify your record
        ReportID = 'RPT'+{FiscalYear}+{0s}+{Auto Incremented ID}
    */

    SELECT @LoopCounter = min([Serial])
    FROM #T
    WHERE [Serial] > @LoopCounter
END