Database objects often undergo changes to their structure over the life-cycle of the product. These changes include, but are not limited to, the addition and removal of columns and parameters. Whenever such changes are made, the meta-data for any dependent database objects also needs to be updated if they are not schema-bound. Here’s a small example.

Let us create a data view on the HumanResources.Employee table as shown below:

USE AdventureWorks2012 ;
GO

--Create a non-schema bound view for the HumanResources.Employee table
CREATE VIEW HumanResources.uview_Employee
AS  SELECT  he.*
    FROM    HumanResources.Employee AS he ;
GO

--Select data from the data-view
SELECT  TOP 1 *
FROM    HumanResources.uview_Employee ;
GO

Now, let us add a column to the HumanResources.Employee table. This would be a common scenario is a service release – as new functionality gets introduced to the system, tables are modified to accommodate to the changes.

USE AdventureWorks2012 ;
GO
--Add a dummy column to the underlying table
ALTER TABLE HumanResources.Employee
ADD ExternalReferenceNumber VARCHAR(20);
GO

--Add some default data to the table
UPDATE HumanResources.Employee
SET ExternalReferenceNumber = ('EXT' + CAST(BusinessEntityID AS VARCHAR(10)));
GO

Because the data view has been created using a SELECT * clause, one would assume that the newly added column will be available in the results. Let us execute the view to confirm this assumption:

USE AdventureWorks2012 ;
GO
--Select data from the data-view
--Notice that the newly added column is not available
SELECT  TOP 1 BusinessEntityID, ExternalReferenceNumber
FROM    HumanResources.uview_Employee ;
GO

Msg 207, Level 16, State 1, Line 3

Invalid column name ‘ExternalReferenceNumber’.

As can be seen from the message above, the newly added column is in fact NOT included in the results. This is because the view is not schema-bound and the underlying meta-data is not yet updated.

To explicitly refresh the view meta-data (in cases where the view cannot be rebuilt), a system stored procedure sp_refreshview needs be used.

--Now, execute sp_refreshview
EXEC sp_refreshview N'HumanResources.uview_Employee' ;
GO

Now, let us attempt to select data from the view again:

--Select data from the data-view
--Notice that the newly added column is now available
SELECT  TOP 1 BusinessEntityID, ExternalReferenceNumber
FROM    HumanResources.uview_Employee ;
GO

As can be seen from the screenshot above, the newly added column is now available in the results of the view.

A word of caution

While this particular example uses SELECT * in the view definition for demonstration purposes, it is recommended to always define the columns explicitly. Explicitly defining columns required will cause errors if a column is dropped and will prevent any unexpected behavior if new columns are added. Alternatively, schema binding can be used.

Until we meet next time,

Be courteous. Drive responsibly.