Unexpected results when querying a SQL View


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



image

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.

image

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

image

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.

Advertisements
This entry was posted in Interesting Stuff. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s