Sunday 10 May 2015

Views are not refreshing in SQL Server when there is any schema change?

                It was weird behaviour I observed while working on one of the development project.Let me explain step by step.
  I have created two view as vwTest1  and vwTest2 .
e.g
Create Table Test (Id Int ,Name varchar(100))
                       ---->  Above table is created with two column as Id & Name
Lets create two view with  same structure,

Create View vwTest1
As
Select  Id ,Name  From Test
                     ----> First View with explicit column name has been created.

Create View vwTest2
As
Select *  From Test

  If we see result of these two view will be same i.e it will show only two column.

Now there is new requirement :
    Name needs to change to CustomerName .

Now I am changing structure  of table as per requirement.

i.e
   Sp_Rename 'Test.Name','CustomerName'

Lets see result of table and views.

Select * from Test
  ---> It will have updated name of column.

Select * from VwTest1
  ---> It will have updated name of column.

Select * from VwTest2
  ---> It will have old name of column.

 Why this happened ?
                         When creating a view the metdata of the view is stored in the system tables, this includes the columns in the SELECT statement.  When you don't explicitly name each column, but rather use the wild card, "*", then SQL Server will list the columns that are contained in the underlying table at the time of creation.  If the underlying table(s) are changed by adding or removing columns after a view has been created then this can affect the view as the metadata of those columns are not automatically updated, if the view is created WITH SCHEMABINDING then the us of the wildcard is prohibited. 

Solution :
 If we have view which uses " * " in its creation then we need manually execute Sp_refreshview
to refresh metadata of views .

e.g
                  exec sp_refreshview VwTest2
Now if we see result  of vwTest2   we can see latest column .






First Database In Sql Server