Error when calling sp_refreshview on view based on two temporal tables

Question

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]

Answer 1

This is a bug that has been previously reported without a fix so far.

People recommend not using SELECT * of course, but that's no excuse for refresh not working.

You don't even have to modify an underlying table—refreshing the view still breaks.

In my opinion, the temporal tables feature is incomplete and plagued with inconsistencies like this.

---

Related Q & A:

• Schema binding seems to not be enforced for view doing temporal query
• View doesn't recognise a change to an underlying table when an existing column is dropped and replaced with one with the same name but as computed
• Why do temporal tables log the begin time of the transaction?
• Why MERGE doesn't insert more than 277 records into a table which is configured with temporal table and a non-clustered index on history table

• jevans: Thanks for the link, this does answer my question. unfortunately the second table is completely unique and as such we need to do a select.* on it as we do not know the column names beforehand (unless I can come up with another way to select them that instead excludes the known columns). To me feels temporal tables are not quite there yet. Thanks again for the info.