For many years now I have been aware that views may need refreshing. If you create a view without using the “SCHEMABINDING” clause, you will need to refresh the view if any of the underlying objects change (e.g. change the length of a column). This can be achieved by using either sp_refreshview or sp_recompile.
This past week I got reminded about this in a most unexpected situation. At a customer who is using Microsoft Dynamics CRM, we had a need to create some views to abstract others from the sometimes complicated nature of a CRM database. Because the owners of the CRM database did not want us to add these views to the actual CRM database, we created a new database purely to house the views. This database is guaranteed to live on the same SQL instance as the CRM database so we simply created the views with cross database queries (e.g. select columnA from SourceDB.dbo.Table1). This solution has been working fine for months. I should mention that this environment was a test environment (NOT Production).
Where it went pear-shaped was when they decided to rename the CRM database and restore from a recent backup. The restored database was named the same as the original CRM database name. Without giving it any major thought, I assumed that this would be a seamless change as far as the views go. I was wrong.
To cut a long story short, the views kept on working, but to our dismay we eventually realised that the data being returned was from the old renamed database, not the restored one. Since renaming a database is simply a metadata change, the views continued to point to the original database. Although I now understand why this is, it caught me off guard and I feel that this is a situation that Microsoft should address.
Below are some scripts that will allow you to view this behaviour for yourself. These assume that you are a sysadmin on the server you’re using. These scripts were run against SQL Server 2008 R2 SP1.
Firstly, create a source database with a simple table and one row of data (make sure that you adjust the physical location of the data files below):
use master
go
create database SourceDB
ON (NAME = SourceDb, FILENAME = ‘ <Folder>\SourceDB.mdf’)
LOG ON (NAME = SourceDb_Log, FILENAME = ‘<Folder>\SourceDB_Log.ldf’)
go
use SourceDB;
go
create table SourceTable (ID int, Name nvarchar(10));
insert into SourceTable(ID, Name)
values(1, ‘Craig’);
Now create the database containing a view:
use master
go
create database ViewDB;
go
use ViewDB;
go
create view vSourceTable
as
select ID, Name
from SourceDB.dbo.SourceTable;
go
If you select the data from the view now you’ll see the data that we inserted into the database:
select ID, Name from vSourceTable;
Now rename the SourceDB:
use master
go
ALTER DATABASE SourceDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
exec SP_RENAMEDB ‘SourceDB’, ‘SourceDB_old’
Go
ALTER DATABASE SourceDB_old SET MULTI_USER
GO
At this point some might think that the view should fail if you query it, but it doesn’t. It returns the same result set as before.
Now create a new database with the same name as the original but with different data:
use master
go
create database SourceDB
ON (NAME = SourceDb_New, FILENAME = ‘C:\Databases\Database Files\SourceDB_New.mdf’)
LOG ON (NAME = SourceDb_Log_New, FILENAME = ‘C:\Databases\Database Files\SourceDB_Log_New.ldf’)
go
use SourceDB;
go
create table SourceTable (ID int, Name nvarchar(10));
insert into SourceTable(ID, Name)
values(2, ‘Bryden’);
If you query the view it is still showing the original values:
select ID, Name from vSourceTable;
In order to get the view to start working on the correct database, we need to refresh the view:
exec sp_refreshview ‘vSourceTable’
Now if we query the view, it is using the correct source:
select ID, Name from vSourceTable;
Strange but true!
