SQL Server Portal

Table is of the frequently used objects in any database. I don not remember seeing any database without a table in my career :). It allows us to keep data inside it and can manipulate it later by using views, procedure & functions etc. However, we should be very careful while customizing a table because sometimes other objects are dependable on a table and can give consistency issues as well.

Today, I came across a similar issue with one of my clients. We customized one table for them and they complained that these customizations are not reflecting in the view which is the reference table. Strange ? (Not exactly)

Let me explain the problem and its solution step by step.

Step 1 :
First of all, we need to create a table and insert few records in it. This table will be used for reference in the view in later step.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP TABLE [dbo].[tbl_Student]
--GO
CREATE TABLE [dbo].[tbl_Student](
[Sno] [int] NOT NULL,
[Student ID] nvarchar(6) NOT NULL ,
[Student name] [varchar](50) NOT NULL
)
Insert into dbo.[tbl_Student] VALUES (1,'STD001','Bob')
Insert into dbo.[tbl_Student] VALUES (2,'STD002','Alexander')
GO
SELECT * FROM [tbl_Student]
GO
--OUTPUT

Step 2 :
Once you created a table, lets create a view on top of it. Given below is the script.
Please make a note that it is not recommended to create view using “*“. It must be with the proper column name of the table.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP VIEW vw_Student
--GO

CREATE VIEW vw_Student
AS
SELECT * FROM [tbl_Student]
GO

SELECT * FROM vw_Student
GO
--OUTPUT

Step 3 :
Once you created a table & view and noticed that schema and table data is same whether in table or view, lets now alter the table and add one column namely “Address” and observe the impact on view . Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
ALTER TABLE [tbl_Student] ADD [Address] varchar(100)
GO
SELECT * FROM [tbl_Student]
GO
SELECT * FROM vw_Student
GO
--OUTPUT

Step 4 :
Oopss…… If you look at the above result set you can observe that whatever changes you made in the table, did not reflect in the view. So what to do ? Here it comes to sp_refreshview (A system level stored procedure), which refreshes the metadata of any view once you edit the schema of the table. Lets execute the sp_refreshview

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
EXEC sp_refreshview N'dbo.vw_Student'
GO
SELECT * FROM [tbl_Student]
GO
SELECT * FROM vw_Student
GO
--OUTPUT

Conclusion :
The bottom line of this article is, whenever you alter any table and if there are views on top of it, do not forget to refresh the metadata of the views using sp_refreshview.

Read Full Post »