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

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.

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:

1. Refresh the views using sp_refreshview stored procedure
2. Do not use SELECT * but use SELECT columnnames
3. 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)