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,
SQLTwins by Nakul Vachhrajani 