将现有列更改为"计算并持久化"，而不删除表/列

Question

我想在不删除表/列的情况下，将SQL Server中的现有列更改为计算和持久化。

我有一个自动递增的ID和另一列ReportID，它是用这种计算格式化的：

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

例如：

• 对于ID=1，2。。。。，10，11。。。，100、101
• ReportID将是RPT2122000001、RPT2122000002、…、，。。。，RPT2122000010，RPT2122000011。。。，RPT2122000101、RPT2122000102

以前，我们在一个"；插入后"；trigger-计算值并更新行。但是通过这样做，当负载很高时，一些ReportID会被复制，并且报告是由不同的用户并行生成的。

因此，为了解决这个问题，我想将现有列更改为具有'RPT'+{FiscalYear}+{0s}+{Auto Incremented ID}的计算列，但问题是我希望现有数据保持不变。因为如果现在进行计算，上一年的所有数据都会被修改为当前财政年度，这是错误的。

当我在ManagementStudio中直接尝试设置计算值时，它也在后台内部运行拖放操作。

我看到了很多答案，但都不够令人满意。

我试图用计算出的值创建一个新列，然后尝试重命名，这也是不允许的。

编辑1

错误：

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

编辑2

财政年度计算：

(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)

还有，有没有办法不创建一个新的列？

Answer 1

我想通过以下方法来解决这个问题：

1. 重命名原始列
2. 创建新的计算列，该计算列使用原始列，并且如果原始列没有值(为null(，则将故障转移到计算

类似：

-- === 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

在我的示例中

试试这个，

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')

步骤1

，重命名旧列

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

步骤2：获取最大自动递增的id列值。

步骤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;

步骤4尝试新的插入

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

你可以用你自己的逻辑来计算财政年度，它会起作用的。

Answer 3

由于您正在计算会计年度，您可以将会计年度计算放入标量函数中，并在计算列中使用它：

1. 使函数用于财政分析计算：

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

然而，在您的会计年度计算中，您应该使其取决于输入的param日期(而不是getdate(((，并且在您的计算列中，您为该行传递类似reportdate或insertdate的内容。

因为如果您的计算是基于getdate((，那么明年同一行的computed列的值将发生更改。这不是你想要的。

使用函数也会被认为是不确定的，并且计算列不能持久化。

然后跟进其他人所说的：

2. 重命名旧列：

exec sp_rename 'test_reports.report_id', 'original_report_id'

3. 添加另一列以保存报告日期

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

你可以为它设置一个默认值，不需要插入/更新就可以修改

4. 添加新的计算列：

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

很长一段时间前，我遇到了与您类似的问题，下面的解决方案对我有效。

您可能会遇到这个问题，因为在大容量插入的情况下，它只会用上次更新的值更新列的值(对于所有行(，因此您会得到重复的值。

考虑到插入触发器之后的现有结构，其中一种方法是继续使用触发器并尝试迭代INSERTED表

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