主页 / dba / 问题 / 340413
Accepted
Geezer
Asked: 2024-06-21 18:04:22 +0800 CST

当现有列被删除并替换为同名但经过计算的列时，视图无法识别对基础表的更改

  • 772

我的理解是，当创建一个视图时，其定义存储在元数据 sys.tables 中。即sys.views。

此外，如果您使用SELECT * FROM它将存储精确的列名，例如SELECT a, b FROM。

即使您使用“CHECK OPTION” - 它仍然不会根据底层表进行验证。

另一方面，“SCHEMABINDING”将根据底层表进行验证。

我的问题是，当表中的某一列dropped被替换为同名列时，但是computed当您查询基于该表的视图时会发生一些奇怪的事情。

这是一个例子。

DROP TABLE IF EXISTS dbo.Test1;
DROP TABLE IF EXISTS dbo.Test2;
GO
CREATE TABLE dbo.Test1
(
    Id          INT IDENTITY(1,1) PRIMARY KEY ,
    Test1Col1   VARCHAR(80) NULL ,
    Test1Col2   VARCHAR(80) NULL ,
    Test1Col3   VARCHAR(80) NULL 
);
CREATE TABLE dbo.Test2
(
    Id          INT IDENTITY(1,1) PRIMARY KEY ,
    Test2Col1   VARCHAR(80) NULL ,
    Test2Col2   VARCHAR(80) NULL ,
    Test2Col3   VARCHAR(80) NULL ,
    Test1Id     INT 
);
GO
INSERT INTO dbo.Test1
    (Test1Col1, Test1Col2, Test1Col3)
VALUES 
    ('Test1Col1Data1', 'Test1Col2Data1', 'Test1Col3Data1') ,
    ('Test1Col1Data2', 'Test1Col2Data2', 'Test1Col3Data2') ,
    ('Test1Col1Data3', 'Test1Col2Data3', 'Test1Col3Data3') ;
GO
INSERT INTO dbo.Test2
    (Test2Col1, Test2Col2, Test2Col3, Test1Id)
VALUES 
    ('Test2Col1Data1', 'Test2Col2Data1', 'Test2Col3Data1', 1) ,
    ('Test2Col1Data2', 'Test2Col2Data2', 'Test2Col3Data2', 2) ,
    ('Test2Col1Data3', 'Test2Col2Data3', 'Test2Col3Data3', 3) ;
GO

创建基于表的视图。

CREATE OR ALTER 
    VIEW    dbo.View1 
AS

    SELECT T1.*, T2.*
    FROM    (
            SELECT  TestId = T.Id
            FROM    dbo.Test1   T
        )   T1
    INNER JOIN  dbo.Test2   T2  ON  T2.Test1Id = T1.TestId ;
GO
SELECT  * FROM dbo.View1 ;
GO

您将获得以下结果集，

在此处输入图片描述

现在我ALTER表dbo.Test2。

DROP列Test2Col3 并将其替换为computed同名的列。

ALTER TABLE dbo.Test2
    DROP COLUMN Test2Col3 ;
ALTER TABLE dbo.Test2
    ADD Test2Col3 AS Test2Col1;
GO

现在，当我查询视图时，我得到以下结果集。

SELECT  * FROM dbo.View1 ;
GO

在此处输入图片描述

列中的数据Test2Col3看起来Test1Id已左移 1。

我期望看到的Test2Col3正在被展示Test1Id，反之亦然。

在此处输入图片描述

这是为什么？
我知道刷新视图或更改视图会更正此问题，但列名并未更改。我已在 SQL-2022 和 Azure SQL 数据库中复制了此问题。

sql-server

3 个回答

  1. 我知道刷新视图或更改视图可以纠正这个问题，但列名并没有改变。 老兄

    您也没有告诉引擎您关心哪些列。您说select *。

    其他地方已经对此进行了详细的描述，但 TL;DR：“不要使用select *”。

    在 OP 示例中，它有点复杂，但修改Martin 的示例，我们可以看到，在视图定义中按名称指定列，我们可以跳过绑定错误，而不需要sp_refreshview注意差异......

    CREATE OR ALTER VIEW dbo.View1
  AS 
    SELECT * 
    FROM  dbo.Test;
GO
CREATE OR ALTER VIEW dbo.View2
  AS 
    SELECT A,B,Num 
    FROM  dbo.Test;

    完成 drop-n-swap 之后，select *从每个视图中可以看出......

    视图1

    A 乙 數量
    乙 10 C

    视图2

    A 乙 數量
    C 乙 10

    db<>小提琴

    • 10
  2. Best Answer

    当您删除并重新创建该列时，该列会在表的末尾创建，因此底层SELECT查询的结果的列会发生移动。

    SELECT T1.*, T2.*
FROM    (
        SELECT  TestId = T.Id
        FROM    dbo.Test1   T
    )   T1
INNER JOIN  dbo.Test2   T2  ON  T2.Test1Id = T1.TestId ;

    但是视图的列元数据没有更新（如返回的）

    SELECT * 
FROM sys.columns 
WHERE object_id = object_id('dbo.View1')

    从视图返回的列使用的列别名基于基础查询和定义的列的列序数。

    它不会根据定义的名称和底层查询返回的列名来匹配它们。

    如果底层查询意外返回许多列，则“额外”的列将被忽略，如果返回的列太少，则会出错

    视图或函数“dbo.View1”指定的列名多于定义的列名。

    一个更简单的例子是

    DROP TABLE IF EXISTS dbo.Test;

GO
CREATE TABLE dbo.Test
(
    A    CHAR(1) NOT NULL  ,
    B    CHAR(1) NOT NULL  ,
    Num  INT NOT NULL   
);
GO

INSERT INTO dbo.Test(A, B, Num) VALUES ('A', 'B', 10);

GO

CREATE OR ALTER VIEW dbo.View1 AS SELECT * FROM  dbo.Test;

GO

ALTER TABLE dbo.Test DROP COLUMN A ;

ALTER TABLE dbo.Test 
        ADD C CHAR(1) NOT NULL DEFAULT 'C',
            D CHAR(1) NOT NULL DEFAULT 'D';

SELECT  * FROM dbo.View1

    返回

    A 乙 數量
    乙 10 C

    它很乐意将 和 用作别名B。并且A包括新添加的列，该列的序数为 3，因此将其用作别名（显然不是）。NumBCNumint

    列D由底层查询返回SELECT *，但属于“额外”列，因此被忽略。执行计划对此进行了优化，并且该列不在表的输出列列表中Test（甚至不在输入树中）。

    • 9

  3. 刷新视图

    更新指定的非架构绑定视图的元数据。视图所依赖的基础对象发生更改可能会导致视图的持久元数据过期。

    备注部分继续（重点是我的）：

    如果视图不是使用 SCHEMABINDING 创建的，则在对视图底层对象进行更改（这会影响视图的定义）时应运行 sp_refreshview。否则，查询视图时可能会产生意外结果。

    在你的情况下

    exec sys.sp_refreshview @viewname = N'dbo.View1'

    在此处输入图片描述

    • 1

相关问题