Wednesday, January 18, 2012

Updating View Schema or Refreshing Views

After creating View, If underlying table schema has been changed will it reflect in the view ?
the answers is NO because SQL Server stores only metadata information describing the view, its columns,security and dependencies when you create View.

But SQL server provides another way to refresh view after updating underlying table schema,by using the sp_refreshview stored procedure we can refresh view.

The below example shows about refreshing view

CREATE TABLE dbo.emp(id int, emp_id int);
INSERT INTO dbo.T1(id , emp_id) VALUES(1, 2);
CREATE VIEW dbo.view_test
SELECT * FROM dbo.emp;

In the above example SQL Server stored metadata information about the columns that existed at that point in time.

Run the following code to query the view

SELECT * FROM dbo.view_test;

You get the following output with both columns:

id emp_id
1 2

Next, add a column to emp:
ALTER TABLE dbo.emp ADD emp_name varchar(100)

Now the schema change in emp was not reflected in the view’s metadata information. As far as SQL Server is concerned, the view still has just two columns. If you execute the previous
SELECT query again, you still get only two columns in the output:

id emp_id
1 2

To refresh the view’s metadata information, run the sp_refreshview stored procedure
against view_test

EXEC sp_refreshview 'dbo.view_test'

Execute the previous SELECT query again, and you get the following output, which includes the new column

id emp_id emp_name
1 2 NULL

No comments:

Post a Comment