SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4

September 15, 2010

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

This is very well known limitation of the View.

Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.

To test this, we will create a view where we will use SELECT * and select everything from the table. Once the view is created, we will add a column to the view. We will test that even though we have used SELECT *, the view does not retrieve the newly added column. Once we refresh the view using SP_REFRESHVIEW, it will start retrieving the newly added column.

Run the following T-SQL script in SQL Server Management Studio New Query Window:

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[LimitView4]'))
DROP VIEW [dbo].[LimitView4]
GO
-- Create View
CREATE VIEW LimitView4
AS
SELECT *
FROM HumanResources.Shift
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Add Column to original Table
ALTER TABLE HumanResources.Shift
ADD AdditionalCol INT
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Refresh the view
EXEC sp_refreshview 'LimitView4'
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Clean up
ALTER TABLE HumanResources.Shift
DROP COLUMN AdditionalCol
GO

Above query will return following resultset.

SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4 LimitView_4

The same limitation exits in the case of deleting the column as well. This is a very well-known issue with the Views. The resolutions of these issues are as follows:

Refresh the views using sp_refreshview stored procedure
Do not use SELECT * but use SELECT columnnames
Create view with SCHEMABINDING; this way, the underlying table will not get modified.

I am enjoying writing this series of blog posts on the limitation of the views. Do let me know if there is any other limitation you would like to read on this blog.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Scripts, SQL Server, SQL View

SQL SERVER – Disabled Index and Index Levels and B-Tree

SQLAuthority News – How to Subscribe to this Blog?

13 Comments. Leave new

Ramdas
September 15, 2010 8:04 am
A nice tip in terms of using the sp_refreshview stored procedure.
Thank you
Reply
Krzysztof Lukowski
September 15, 2010 10:50 am
Hi,
What do you think about joining views? I think, this is bad practise. They are very hard to debag and to find issuse. However, in company that I work for, joining views is regular practise.
Reply
MaximNoronha
September 15, 2010 6:51 pm
Thank you for informing us about sp_refreshview
Reply
Frank
September 15, 2010 7:31 pm
I came across the same issue recently as well and got around it by generating the ALTER VIEW script through SSMS and re-executing it with no changes. I didn’t know about sp_refreshview. Thanks!
Reply
janki
September 15, 2010 7:55 pm
Pinal,
does that mean, that each time when modification is made to the table, views need to be refresh?
There may be cases when, one doesn’t realizes or remember to refresh views.
Do we have anyother options to avoid this kind of situations?
Reply
janki
September 15, 2010 8:13 pm
Yeah, I got it.
Tried out – cleared my doubt.
Good Article.
Thanks
Reply
Jamie Thomson
September 20, 2010 3:22 am
I think you need to better emphasize the folly of using SELECT * in a view definition (or, in fact, anywhere in released code). If it were up to me writing
CREATE VIEW v AS
SELECT * …
would throw an error.
Regards
Jamie
Reply
Narendra
October 4, 2010 2:12 pm
After Removing the Additional Column Which Added By Using The ALTER Command,We Have To Refresh The
View Again,Otherwise, It Wil Not Display The Records In The View.
Thank You
Reply
Aarti
April 23, 2011 12:43 pm
This limitation asked me in one of my interview and i could not able to answer it.
Then i searched this limitation in your blog and i found the article related with it.
All right now i know more about View
Pinal you are the best
Thanks
Reply
Karpagavalli.V
December 22, 2011 7:28 pm
Thank you… so fantastic answers.. U fulfilled all my doubts..
Reply
Shailesh
May 10, 2013 1:27 pm
Thanks…This blog helped much…!!!
Reply
thillai
October 31, 2014 12:57 pm
Sir in Sql sever 2000 “sp_refreshview” is supported or not?
Reply
Nabil
September 15, 2015 2:24 am
We had a case where a view was created like so:
select A.*, intcolumn from A
inner join B
on …
(intcolumn was coming from table B)
Now, varcharcolumn was added to table A.
When run, the view was returning intcolumn data from A.varcharcolumn, not from B.intcolumn. The data type mismatch made this even stranger, but SQL was happy to return the data. Other items downstream failed that were expecting int data, but got varchar instead.
Running sp_refreshview fixed it. Thanks again.
Reply