Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

SQL Server Views: Some Great Views about Views

  • 3 May 2012
  • Author: Pradeep Dahiya
  • Number of views: 4259
  • 0 Comments

Everybody has different views about VIEWS, that’s what make them interesting topic to discuss.  Other thing is if you make some assumptions about VIEWS, they can lead you to problems.  As my target audience is SQL beginners, today I’ll talk about few things about VIEWS so some of the obvious mistakes can be avoided.   Actually there are so many things we can talk about VIEWS, but I leave them for future posts.

Today I’ll show what happens when you create a view using “SELECT * FROM TableName” and then ALTER the underlying table used in the VIEW.   The normal assumption most of the SQL beginners make is:  If you create a VIEW using “SELECT * FROM TableName”, all the changes made in the underlying table will automatically reflect in the VIEW as well.

Case 1: You add a column to the table.  
Now here is a question for you:  If you have a table named Employees and a VIEW created  top of it with simple query “SELECT * FROM Employees”.  Now if I add one column to Employees table, will that column appear in result if I execute query “SELECT * FROM View”?

I had ask this question many times in interviews; 80% of time I heard a thumping “Yes”.  And most of the candidate were having experience of well over 3 years.  Answer to above question is BIG NO.  Let me explain this with actual example, 1st lets create some sample data.

------------------------------------------------------------------------------------------------------

-- Create the table with 4 columns 
CREATE TABLE
 Employees 

EmpID INT
FirstName VARCHAR(6), 
LastName VARCHAR(20), 
ManagerID INT


GO

-- Populate the table with sample data 
INSERT INTO Employees SELECT 
1, 'Mangal', 'Pardeshi', 0 UNION ALL SELECT 
2, 'Rahul''Sharma', 1 UNION ALL SELECT 
3, 'Ajay''Varma',2 
GO

-- Create a View with wild card * 
CREATE VIEW Emp AS 
SELECT * 
FROM Employees 
GO

-- Verify the data in the table and view 
SELECT
 * 
FROM Employees 
GO 
SELECT * 
FROM Emp 
GO
 
------------------------------------------------------------------------------------------------------

Data in the table and view: 
test data

As you can see the data  in the Table and View is identical, so are the number of columns.  Now lets add one column HireDate in the table and see what happens.

------------------------------------------------------------------------------------------------------ 
-- Add HireDate Column in Employees Table 
ALTER TABLE Employees ADD HireDate DATETIME 
GO

-- Update the HireDate column with some date 
UPDATE Employees 
SET HireDate = '20100827' 
GO

-- Again Verify the data in table and View 
SELECT * 
FROM Employees 
GO 
SELECT * 
FROM Emp 
GO 
------------------------------------------------------------------------------------------------------

add column

As you see HireDate column didn’t appear in VIEW.  So question arises: Why a VIEW doesn't get refresh when I add a column to the table?  
Short answer is when you create the VIEW, the column information/definition of VIEW(metadata of VIEW) gets stored in system tables at the time of creation of VIEW.  And that metadata doesn’t get refresh when you alter the underlying table.  You have to explicitly refresh the metadata of VIEW.  
So next question is: How to refresh the VIEW once you modified the underlying table? 
There are 2 ways to refresh the VIEW: 
1.  Using the system stored procedure sp_refreshview 
From Books Online - sp_refreshview: Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends. 
Syntax: EXECUTE sp_refreshview ‘viewname’

2. Or by executing ALTER VIEW statement.  When you ALTER the VIEW, SQL Server will pick the latest column definition from underlying table and will update the VIEW metadata.

So in our case we can refresh the VIEW Emp by:

------------------------------------------------------------------------------------------------------ 
-- 1. using system stored procedure sp_refreshview 
EXECUTE sp_refreshview 'Emp' 
GO 
-- 2. Or by executing the ALTER statement 
ALTER VIEW Emp 
AS 
SELECT
 * 
FROM Employees 
GO

-- Verify the data in View 
SELECT * 
FROM Emp 
GO 
------------------------------------------------------------------------------------------------------

Note you don’t need to execute both the queries, either of the above can do the trick for you.  Now you can see the HireDate column in Emp view as well: 
view

Case 2: You drop a column from the table. 
Similarly when you drop a column from the table, the VIEW definition doesn’t get updated even though you have used wild card “*” in VIEW definition.  Now lets drop the column HireDate from the table and see what happens: 
Note: In previous step I refreshed the View after adding the HireDate, so now HireDate is part of Emp view as well.

------------------------------------------------------------------------------------------------------ 
-- First Verify the data before dropping column 
SELECT * 
FROM Employees 
GO 
SELECT * 
FROM Emp

-- Drop HireDate column from Table 
ALTER TABLE Employees DROP COLUMN Hiredate 
GO

-- Verify the data after dropping the column. 
SELECT * 
FROM Employees 
GO 
SELECT * 
FROM Emp 
GO
 
------------------------------------------------------------------------------------------------------

 

Now in this case HireDate column will get removed from table but the metadata of the VIEW still have its information stored so you will get the following error on selecting data from VIEW. 
Msg 4502, Level 16, State 1, Line 1 
View or function 'Emp' has more column names specified than columns defined.

The solution is, again refresh the VIEW definition by executing:

------------------------------------------------------------------------------------------------------ 
EXECUTE sp_refreshview 'Emp' 
GO
 
------------------------------------------------------------------------------------------------------

Now you won’t get the same error.

Case 3: You drop one or more columns and add equal or more number columns to the table. 
This case is even more dangerous as User selecting data from the VIEW can get wrong data under wrong columns and can create confusion.  I won’t explain this in detail, just execute following queries and you will realize what I’m saying.

------------------------------------------------------------------------------------------------------ 
-- Drop column LastName from table 
ALTER TABLE Employees DROP COLUMN LastName 
GO 
-- Add Column DeptName with default value 'IT' 
ALTER TABLE Employees ADD DeptName VARCHAR(10) NOT NULL DEFAULT 'IT' 
GO

-- Verify the data 
SELECT * 
FROM Employees 
GO 
SELECT * 
FROM Emp 
GO 
------------------------------------------------------------------------------------------------------

Final

 

Now if you see, the LastName column is not present in the table and you can also see the DeptName column in the table.  And interesting observation with VIEW is, though the data is exactly matching with the table but columns names are not correct.  We again need to refresh the VIEW to correct it.

------------------------------------------------------------------------------------------------------ 
EXECUTE sp_refreshview 'Emp' 
GO

-- Verify the data 
SELECT * 
FROM Emp 
GO 
------------------------------------------------------------------------------------------------------

Back to normal now: 
Last

What is the solution?  
Obvious prevention is don’t use wild card “*” while creating VIEWS.  But even listing out columns is just a prevention or I’d say it is a good practice.   Because even after listing out the columns, if you drop a column from the table that has been used in any VIEW you will still face problems.

The solution is creating the view using “WITH SCHEMABINDING” option. 
From Books Online:  Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

Let me show you one example with SCHEMABINDING:

------------------------------------------------------------------------------------------------------ 
-- Create View with SCHEMABINDING 
CREATE VIEW NewEmp 
WITH SCHEMABINDING 
AS 
SELECT
 EmpID, 
    FirstName 
FROM dbo.Employees 
GO

-- Verify the data 
SELECT * 
FROM NewEmp 
GO

-- Now try to alter the table 
ALTER TABLE Employees DROP COLUMN EmpID 
GO 
------------------------------------------------------------------------------------------------------

Now you will get the error: 
Msg 5074, Level 16, State 1, Line 2 
The object 'NewEmp' is dependent on column 'EmpID'. 
Msg 4922, Level 16, State 9, Line 2 
ALTER TABLE DROP COLUMN EmpID failed because one or more objects access this column.

Basically  WITH SCHEMABINDING has prevented the change that would affect the view definition.

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

Please login or register to post comments.