Systems Engineering and RDBMS

Refreshing the definition of a view

Posted by decipherinfosys on March 5, 2008

As you know, a regular view is just a logical definition which is stored in the meta-data tables. When the underlying table structures are changed, those additions do not directly reflect in the meta-data definitions of the views. If you are following the best practices (not using a “SELECT *” in the views and using an explicit column list, creating views with an explicit SCHEMABINDING option) then this becomes a non-issue since you would then need to make the changes explicitly to those view definitions. However, we have seen at client shops that some people still use the “select *” definition for the views and assume that when the DDL changes are made to the underlying table structures, those will automagically show up in the view definitions as well. That is not the case. Let’s check that out using an example:

Let’s create two tables and link them together as a parent and a child:

CREATE TABLE T1 (COL1 INT PRIMARY KEY, COL2 INT);
CREATE TABLE T2 (COL3 INT PRIMARY KEY, COL4 INT, CONSTRAINT FK_T2_TO_T1 FOREIGN KEY (COL4) REFERENCES T1(COL1));
GO

Now, let’s create a simple view which has an inner join between the two tables and uses a “SELECT *” in the definition.

CREATE VIEW T3 AS SELECT * FROM T1, T2 WHERE T1.COL1 = T2.COL4;
GO
SET NOCOUNT ON
GO
INSERT INTO T1 VALUES (1, 10);
INSERT INTO T1 VALUES (2, 20);
INSERT INTO T1 VALUES (3, 30);
INSERT INTO T2 VALUES (100, 1);
INSERT INTO T2 VALUES (200, 1);
INSERT INTO T2 VALUES (300, 1);
INSERT INTO T2 VALUES (400, 3);
INSERT INTO T2 VALUES (500, 3);

Now, let’s look at the data through the view:

SELECT * FROM T3

COL1 COL2 COL3 COL4
———– ———– ———– ———–
1 10 100 1
1 10 200 1
1 10 300 1
3 30 400 3
3 30 500 3

Let’s follow that up by changing the definition of the T2 table by adding another column to it:

ALTER TABLE T2 ADD COL5 INT;

Now that the column has been added to the underlying table, does that automagically reflect in the view definition just because we created it using a “SELECT *”?

SELECT * FROM T3

COL1 COL2 COL3 COL4
———– ———– ———– ———–
1 10 100 1
1 10 200 1
1 10 300 1
3 30 400 3
3 30 500 3

As you can see from above, that is not the case. One can then either ALTER the definition of the view using the ALTER view command or just do a DROP/CREATE or in SQL Server, one can also use the system stored procedure called sp_refreshview to refresh the definition of the view and in this case, since the view is using a “SELECT *”, it will change the meta-data to include the newly added column as well:

sp_refreshview t3
go

select * from t3

COL1 COL2 COL3 COL4 COL5
———– ———– ———– ———– ———–
1 10 100 1 NULL
1 10 200 1 NULL
1 10 300 1 NULL
3 30 400 3 NULL
3 30 500 3 NULL

So, if you have views that have a “SELECT *” built into them, our first recommendation is to adhere to good practices and change it to be an explicit column list and also include the SCHEMABINDING option in the view definition which will prevent you from specifying a “SELECT *” in the view definition as well. If this is in a schema that you have inherited and in the short term, you need a way to refresh the views that have a “SELECT *” in them, then you can use the technique shown in this blog post to do the refreshes.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: