The other day I was trying to run a select statement from a view in SQL Server and I got some unexpected errors with the data types in the result set from the view with respect to the base table. When I dig deeper into the view I found a SELECT * in one of the queries used for defining the view. As well know that SELECT * is bad for performance reasons because of the extra I/O s required to read the unnecessary columns and should be avoided. On further observation we identified that the table on which SELECT * was there has recently gone a change in terms of new columns , renaming of columns . Are these the reason for my inconsistency in data types. To understand the problem correctly lets run the following in SSMS.
CREATE DATABASE TEST GO CREATE TABLE TestTable ( ColumnA int , ColumnB int ) CREATE VIEW MyView AS SELECT * FROM TestTable -- Not a good practice
Now insert some records and verify the view
INSERT INTO TestTable VALUES(1,1) INSERT INTO TestTable VALUES (2,2) SELECT * FROM MyView
Now rename the “ColumnB” to “Column_Renamed” and changing the datatype of column B to varchar as below.
EXEC sp_rename 'dbo.TestTable.ColumnB','Column_Renamed', 'COLUMN'; GO ALTER TABLE TestTable ADD ColumnB varchar(10) NOT NULL DEFAULT 'Test' GO
Now run the query to select from the View
SELECT * FROM MyView
You will see the same results as before i.e the new column addition and changing of data types are not reflected in the view and the Column B still shows as integer.
How to avoid this scenario. There are 2 ways you can approach this problem.
1. Use SP_RefreshModule to refresh the View.
EXEC sp_refreshsqlmodule 'dbo.MyView' GO SELECT * FROM MyView
We have to call this for each view dependent on the changed column and if we miss any view then we will get the same unexpected or invalid results as defined above.
2. Use WITH SCHEMABINDING option while creating the view and explicitly selecting the columns from the table. This option will proactively error out if we are doing any datatypes changes in the table without dropping the view.
CREATE VIEW MyView WITH SCHEMABINDING AS SELECT ColumnA,ColumnB FROM dbo.TestTable -- Not a good practice GO;
EXEC sp_rename 'dbo.TestTable.ColumnB','Column_Renamed', 'COLUMN'; GO
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 497 Object 'dbo.TestTable.ColumnB' cannot be renamed because the object participates in enforced dependencies.
Here first you have to drop the view and then modify the columns and then recreate the view. This is more of a proactive approach .
Hope this helps.