Error when calling sp_refreshview on view based on two temporal tables
1
vote
1
answer
95
views
I have two temporal tables joined by a view, and when I alter the second table, and call
sp_refreshview
, it generates the error:
>View or function 'ViewTest' has more column names specified than columns defined.
I noticed that after calling sp_refreshview
, the select *
on the second table is now incorporating the hidden datetime columns meant for system versioning.
Below is the test code that I have written to demonstrate the issue.
USE Test;
ALTER TABLE Department SET ( SYSTEM_VERSIONING = OFF)
GO
ALTER TABLE DepartmentTwo SET ( SYSTEM_VERSIONING = OFF)
GO
DROP TABLE IF EXISTS DepartmentTwo;
DROP TABLE IF EXISTS Department;
CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);
CREATE TABLE DepartmentTwo
(
DeptID INT NOT NULL FOREIGN KEY REFERENCES Department(DeptId),
DeptTwoID INT NOT NULL PRIMARY KEY CLUSTERED,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);
DROP VIEW IF EXISTS ViewTest;
GO
CREATE VIEW ViewTest
AS
SELECT
d.DeptName,
d.ManagerID,
d.ParentDeptID,
dt.*
FROM Department d
LEFT OUTER JOIN DepartmentTwo dt on d.DeptID = dt.DeptID
GO
ALTER TABLE DepartmentTwo ADD NewCol int;
GO
execute sp_refreshview 'ViewTest';
GO
select * from ViewTest;
Is there some specific behaviour on temporal tables that is causing the datetime columns to begin being shown in the view, even though they should be hidden? Below is an example on how it starts incorporating the System version columns (ValidFrom, ValidTwo), with the generated select statement via SSMS:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [DeptName]
,[ManagerID]
,[ParentDeptID]
,[DeptID]
,[DeptTwoID]
,[ValidFrom]
,[ValidTo]
,[NewCol]
FROM [Test].[dbo].[ViewTest]
Asked by jevans
(13 rep)
Jul 25, 2024, 04:09 PM
Last activity: Jul 26, 2024, 08:48 AM
Last activity: Jul 26, 2024, 08:48 AM