Back to the Basics: Refreshing a regular view definition

Anyone who has worked in relational databases for some time knows what a view is.  There are other advanced concepts like materialized views (in Oracle) or indexed views (in SQL Server) but a regular view is something if you are someone who has worked in the IT industry as a developer or a DBA, you must have used it or atleast heard about it.  There are a lot of articles out there on the net which talk about the performance characteristics, pros and cons of using views, views vs synonyms etc.  One thing that we stumbled across while troubleshooting an issue related to the CRM implementation at a client site was in relation to refreshing the definition of a view.

As you know, a regular view is just a logical definition.  So, let’s create a simple view which is formed based on a join condition between two tables:

In SQL Server:

create view dbo.test_vw
as select * from dbo.TEST1 as t1
inner join dbo.TEST2 as t2
on t1.COL1 = t2.COL3

Now, notice that we are using a select * which means that we should get each and every column back from both the tables (and the column names in this case are unique across the tables).  When we execute a simple select on this view:

select * from dbo.test_vw

we get this data-set:

COL1        COL2        ID          COL3        COL4
———– ———– ———– ———– ———–
1           10          1           1           1000
2           10          2           2           1000
3           10          3           3           1000
3           10          4           3           1000
2           10          5           2           1000
4           10          6           4           1000

Now, let us go ahead and add a column to one of the tables:

alter table dbo.Test2 add COLX int null;

Now, if you run the select statement again on the view, you will still get the same output i.e. without the COLX showing up in the output.  So, even though we are using a “select *”, the new addition to the table does not show up in the view output.  In order to make it happen, you have to refresh the view.  We have two options to do it:

1) The obvious one – re-create the view (could be a drop and create or even alter would do).
2) Use the system stored procedure called sp_refreshview.

So, after executing:

sp_refreshview test_vw

when we execute the select off the view again, this time, it will show up the new column.

Same is true in Oracle as well.  Let’s follow that up with an example as well:

CREATE TABLE test
(
col1 VARCHAR2(10),
col2 VARCHAR2(10)
);

INSERT INTO TEST VALUES(‘Oracle’,’10g’);
INSERT INTO TEST VALUES(‘SQLServer’,’2008′);
COMMIT;

CREATE VIEW VW_TEST AS SELECT * FROM TEST;

Selecting data from view will retrieve both the columns.

SELECT * FROM VW_TEST;

COL1       COL2
———- ———-
Oracle     10g
SQLServer  2008

We can even check the status of the view object using following sql.

SELECT object_name, status
FROM user_objects
WHERE object_name = ‘VW_TEST’;

OBJECT_NAME     STATUS
————— ——-
VW_TEST         VALID
Now let us add new column to the table and then check the view.

SQL> ALTER TABLE TEST ADD COL3 NUMBER(9);

Table altered.

SQL> SELECT * FROM VW_TEST;

COL1       COL2
———- ———-
Oracle     10g
SQLServer  2008

Even though new column was added to base table and view was created as select * from table, new column didn’t make it to the view automatically. If we look at the status of the view, it is still in a valid state. In order to in-corporate newly added column to the view, we will have to re-create the view.

SQL> create or replace view Vw_test as select * from test;

View created.

SQL> select * from vw_test;

COL1       COL2             COL3
———- ———- ———-
Oracle     10g
SQLServer  2008

The issue that happened at the client site was that in order to integrate their CRM application (in this case: Sales Logix) with their application, the objects within their application database were made visible within sales logix using views (their provider does not support synonyms).  And when additions were made to the base table definitions, those changes were not available in Sales Logix unless a refresh of the view was done.  So, as part of the change policy, a new step was added which would run the refresh view script in the Sales Logix database whenever there are DDL changes being performed in the application database.