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

8 votes

3 answers

1048 views

sql-server azure-sql-database view computed-column sql-server-2022

                          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.


                        

Asked by Geezer (513 rep)

Jun 21, 2024, 10:04 AM
Last activity: Jun 21, 2024, 07:23 PM

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

Related Questions