Why do I have problems with views after altering the base table?

You may be using SQL Server views to enforce column-level security against base tables, or to reduce programming complexity. What you may have seen is that if you make changes to the base table(s), this can adversely affect the usability of the view. Let's look at a couple of examples: 
 

---

Dropping a column 
 
Obviously, dropping a column on which the view depends can cause problems... whether or not the view mentions the column explicitly. If you run the following code: 
 

CREATE TABLE dbo.splunge  (      splungeID INT,      blat VARCHAR(32)  )  GO    CREATE VIEW dbo.vSplunge  AS      SELECT splungeID, blat          FROM dbo.splunge  GO    EXEC sp_rename 'splunge.blat', 'foo', 'COLUMN'  GO    SELECT * FROM dbo.vSplunge  GO    DROP VIEW dbo.vSplunge  DROP TABLE dbo.splunge  GO

 
Results: 
 

Server: Msg 207, Level 16, State 3, Procedure vSplunge, Line 4  Invalid column name 'blat'.  Server: Msg 4413, Level 16, State 1, Line 1  Could not use view or function 'dbo.vSplunge' because of binding errors.

 
If you use SELECT * in your view, you will see a slightly different error: 
 

CREATE TABLE dbo.splunge  (      splungeID INT,      blat VARCHAR(32)  )  GO    CREATE VIEW dbo.vSplunge  AS      SELECT *          FROM dbo.splunge  GO    ALTER TABLE dbo.splunge DROP COLUMN blat  GO    SELECT * FROM dbo.vSplunge  GO    DROP VIEW dbo.vSplunge  DROP TABLE dbo.splunge  GO

 
Results: 
 

Server: Msg 4502, Level 16, State 1, Procedure vSplunge, Line 4  View or function 'dbo.vSplunge' has more column names specified than columns defined.  Server: Msg 4413, Level 16, State 1, Line 1  Could not use view or function 'dbo.vSplunge' because of binding errors.

 
This is because the view relies partly on the definition of the table as it existed at the time the view was created. 
 

---

Renaming a column 
 

CREATE TABLE dbo.splunge  (      SplungeID INT,      blat VARCHAR(32)  )  GO    CREATE VIEW dbo.vSplunge  AS      SELECT SplungeID, blat          FROM dbo.splunge  GO    EXEC sp_rename 'splunge.blat', 'foo', 'COLUMN'  GO    SELECT * FROM dbo.vSplunge  GO    DROP VIEW dbo.vSplunge  DROP TABLE dbo.splunge  GO

 
Results: 
 

Server: Msg 207, Level 16, State 3, Procedure vSplunge, Line 5  Invalid column name 'blat'.  Server: Msg 4413, Level 16, State 1, Line 1  Could not use view or function 'dbo.vSplunge' because of binding errors.

 

---

Re-creating the table with columns in a different order 
 
This is only a problem if you use SELECT * in your views (which you shouldn't do), but here is a repro: 
 

CREATE TABLE dbo.splunge  (      SplungeID INT,      blat VARCHAR(32)  )  GO    CREATE VIEW dbo.vSplunge  AS      SELECT *          FROM dbo.splunge  GO    DROP TABLE dbo.splunge  GO    CREATE TABLE dbo.splunge  (      blat VARCHAR(32),      SplungeID INT  )  GO    INSERT splunge SELECT 'foo', 1  GO    SELECT * FROM dbo.vSplunge  GO    DROP VIEW dbo.vSplunge  DROP TABLE dbo.splunge  GO

 
Results: 
 

SplungeID blat  --------- ----  foo       1

 
How is this possible? SplungeID is an INT, and blat is a VARCHAR, yet it seems that the view has swapped their values. This is because the view relies on the ordinal position of the columns in the table's current form, but still lists the column names in the order of the original creation. You can avoid this problem by never using SELECT * in your views; in the meantime, you can make the symptom go away by running the following command: 
 

EXEC sp_refreshview 'dbo.vSplunge'  GO

 
This will tell the server about the new table definition and straighten out the ordinal position and data for each column. You could accomplish the same thing by altering the view, or dropping and re-creating. 
 

---

So, how do you avoid these problems? Your first instinct might be to run 3rd party software to monitor all DDL events on the server, and then updating views manually to reflect the changes to the base tables. But there are better ways than merely being reactionary.

• First, never use SELECT * in a view definition. ALWAYS name your columns explicitly. This will take care of both confusion and potential data consistency issues. 
   
  
• More importantly, you can use the WITH SCHEMABINDING attribute for views. See how the above mistakes are avoided: 
   
  

  CREATE TABLE dbo.splunge  (      splungeID INT,      blat VARCHAR(32)  )  GO    CREATE VIEW dbo.vSplunge  WITH SCHEMABINDING  AS      SELECT splungeID, blat          FROM dbo.splunge  GO

   
  Notice that the only change is the WITH SCHEMABINDING option. Now, when I try to drop a table: 
   
  

  DROP TABLE dbo.splunge  GO

   
  Results: 
   
  

  Server: Msg 3729, Level 16, State 1, Line 1  Cannot DROP TABLE 'dbo.splunge' because it is being referenced by object 'vSplunge'.

   
  When I try to drop a column: 
   
  

  ALTER TABLE dbo.splunge DROP COLUMN blat  GO

   
  Results: 
   
  

  Server: Msg 5074, Level 16, State 3, Line 1  The object 'vSplunge' is dependent on column 'blat'.  Server: Msg 4922, Level 16, State 1, Line 1  ALTER TABLE DROP COLUMN blat failed because one or more objects access this column.

   
  And when I try to rename the column: 
   
  

  EXEC sp_rename 'splunge.blat', 'foo', 'COLUMN'  GO

   
  Results: 
   
  

  Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368  Object 'splunge.blat' cannot be renamed because the object participates in enforced dependencies.

   
  So obviously, the WITH SCHEMABINDING option helps keep your views immune from problematic base table changes.