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

Question

My understanding here is that when a View is created, the definition is stored in metadata sys.tables. i.e. sys.views.

Also, that if you use SELECT * FROM it will store the exact column names e.g. SELECT a, b FROM.

Even if you use the "CHECK OPTION" - it still will not validate against the underlying tables.

"SCHEMABINDING" on the other hand will validate against the underlying tables.

My issue is when a column on a table is dropped and replaced with the one with the same name but as a computed then something weird happens when you query a view based on that table.

Here's an example.

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 a view based on tables.

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

You get this result set,

Now I ALTER table dbo.Test2.

DROP column Test2Col3 and replaced it with a computed column with the same name.

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

Now when I query the view, I get the following result set.

SELECT  * FROM dbo.View1 ;
GO

The data in columns Test2Col3 and Test1Id looked to have left shifted 1.

What I would expect to see in Test2Col3 is being shown in Test1Id and vice versa.

Why is this?
I know a view refresh or alter view will correct this but the column names haven't changed. I've replicated this in SQL-2022 and Azure SQL database.

Answer 1

I know a view refresh or alter view will correct this but the column names haven't changed. Geezer

You also didn't tell the engine which columns you care about. You said select *.

It's been written about at length elsewhere but TL;DR: "Don't use select *".

It's a bit more convoluted in the OP example, but modifying Martin's example, we can see that specifying the columns by name in the view definition, we can skip the binding errors without the need for sp_refreshview Note the difference...

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;

After doing our drop-n-swap, select * from each of these views reveals...

View1

A	B	Num
B	10	C

View2

A	B	Num
C	B	10

db<>fiddle

Answer 2

When you drop and recreate the column the column is created at the end of the table so the results of the underlying SELECT query have their columns shifted.

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

The column metadata for the view is not updated though (as returned by)

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

The column aliasing used for the columns returned from the view is based on column ordinals of the underlying query and the defined columns.

It doesn't match them up based on defined names and the column names returned by the underlying query.

If the underlying query returns unexpectedly many columns the "extra" ones are ignored and if it returns too few you get error

View or function 'dbo.View1' has more column names specified than columns defined.

A simpler example is

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

Which returns

A	B	Num
B	10	C

It is quite happy to alias B as A and Num as B. And to include the newly added column C which has ordinal 3 so is aliased as Num (and is clearly not an int).

Column D is returned by the underlying SELECT * query but is an "extra" one so is ignored. The execution plan optimises this out and that column is not in the output column list from the table Test (or even present in the input tree).

Answer 3

sp_refreshview

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

The Remarks section then continues (emphasis mine):

If a view isn't created with SCHEMABINDING, sp_refreshview should be run when changes are made to the objects underlying the view, which affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

In your case

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