我有两个由视图连接的时间表,当我更改第二个表并调用时sp_refreshview
,它会生成错误:
视图或函数“ViewTest”指定的列名比定义的列名多。
我注意到,调用之后sp_refreshview
,select *
第二个表上的现在合并了用于系统版本控制的隐藏日期时间列。
下面是我编写的用于演示该问题的测试代码。
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;
时间表上是否存在某些特定行为,导致日期时间列开始显示在视图中,即使它们应该被隐藏?下面是一个示例,说明它如何开始将系统版本列(ValidFrom、ValidTwo)与通过 SSMS 生成的 select 语句合并在一起:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [DeptName]
,[ManagerID]
,[ParentDeptID]
,[DeptID]
,[DeptTwoID]
,[ValidFrom]
,[ValidTo]
,[NewCol]
FROM [Test].[dbo].[ViewTest]