We sometimes encounter a problem with having "custom" or "one off" objects in databases that deviate from a production version of an object.When updating the production version, we sometimes forget the "custom" ones when we do updates (DML or DDL).

This was a recent case for some tables and views. I tend to be a bit anal about adding new fields into a table: I don't like them inserted into the "end" of a table when the existing last field in the table is some sort of record to indicate an "update" or "archive" date. When querying data in a table using a quick SELECT *, it is easy to scroll to the end of a row to look at the dates. There can also be other cases when a vendor supplying some of our data will insert a field in the middle of their feed, in which case we try to do the same to make data inserts flow easier.

To handle these situations, I created a script to re-name an existing table, create a new table (with the new fields somewhere in the middle), copy the data from the "original" to the "new" table and all is well... or so I thought.

The Problem

One of the report developers recently called about a problem with a view. The data in the table was not matching the data in the view the developer was using to create a custom report for a client. I wrote an example to create the intial objects; the view was created to have the client only view their data, as this particular database (one of hundreds) contains data from multiple clients; most of our databases are single client.

CREATE TABLE dbo.MyTable (
FirstName varchar(10),
LastName varchar(10),
HireDate smalldatetime,
Manager varchar(20),
Updated smalldatetime)
INSERT INTO dbo.MyTable  
SELECT 'Joe','Smith','05/12/09','Frank ','05/11/12' UNION
SELECT 'Sue','Jones','01/15/05','Bill','07/12/07'
CREATE VIEW dbo.VIEW_MyView
AS
SELECT * from dbo.MyTable
WHERE Manager = 'Frank'

Then I did my "magic" and added DepartmentNo varchar(20) to the dbo.MyTable table:

EXEC sp_rename 'dbo.MyTable', 'MyTable_Orig'
CREATE TABLE dbo.MyTable (
FirstName varchar(10),
LastName varchar(10),
Department varchar(20),
HireDate smalldatetime,
Manager varchar(20),
Updated smalldatetime)
INSERT INTO dbo.MyTable (FirstName, LastName, HireDate, Manager, Updated)
SELECT * from dbo.MyTable_Orig
update dbo.MyTable
SET Department = 'IT'
DROP TABLE dbo.MyTable_Orig

In this case, the custom view on these table(s) was no longer aligned with the referenced table.

SELECT FirstName, LastName, HireDate, Manager, Updated 
from dbo.MyTable
SELECT FirstName, LastName, HireDate, Manager, Updated 
from dbo.VIEW_MyView

Results:

FirstName    LastName    HireDate    Manager     Updated

Joe          Smith       05/12/2009  Frank       05/11/2012

FirstName    LastName    HireDate    Manager     Updated

Joe          Smith       IT          05/12/2009  Frank

The data was "shifted" in the view! Inserting the new DepartmentNo field in the table caused the data in the view to "shift". So how do we fix this and why did the problem occur?

The Initial Solution

While researching the problem, I discovered there is a SQL command, sp_refreshview, that can correct the issue.

Using a modified version of code from the BOL explanation on sp_refreshview:

SELECT DISTINCT 'EXEC sp_refreshview ''' + so.name + '''' 
FROM sys.objects AS so 
INNER JOIN sys.sql_dependencies AS sed 
    ON so.object_id = sed.object_id
WHERE so.type = 'V' AND OBJECT_NAME(sed.referenced_major_id) LIKE 'MyTable%'

I took the results of this query and ran sp_refreshview, which corrected the view. In doing further research, it appears that views are "static", which can be problematic when using a "SELECT *" statement, rather then specific field names.

Next Step

It occurs to me that our organization and developers need a better method to monitor modifications on specific objects, either DDL or DML, when then might affect "custom" or "one off" objects. I will be looking into Triggers or some other method to alert us to when we need to ensure any "custom" or "one off" objects are updated also!

Another option is to review the "WITH SCHEMABINDING" option for views, getting rid of the SELECT * (which was simplistic for this example, but can be more complicated in real life!).

Thanks!