Don’t forget to drop your views

I had a fairly puzzling issue today, which took a few minutes to figure out. Some time ago I created a “history” table. This was before temporal tables came out in SQL.

The history table and associated table were paired together so when a change is made to an account you could look at the ValidFrom and ValidTo columns to bill appropriately.

The two tables are in a View with a UNION and this allows for easy access using the ValidFrom / ValidTo as a predicate to see what the price was at any given time.

What does this have to do with dropping views?

Let’s say you get a request or maybe the developer adds a column in the middle of the table. What happens to the view? Was it created with SELECT *? Could the title of this post just as easily have been “Don’t SELECT * ever again!”? Sure…

This is exactly what had happened. The table was altered and the view didn’t change. The view was actually throwing a date from string conversion error.

Let’s take a peek at the aftermath of adding a column without rebuilding the view.

SETUP

First, create a simple table:

CREATE TABLE dbo.demo
(    id int NOT NULL,
Fname varchar(50) NULL,
Lname varchar(50) NULL,
AddressLine1 varchar(50) NULL,
City varchar(50) NULL,
State char(2) NULL,
Zip varchar(10) NULL,
ModifiedDate datetime NULL,
CONSTRAINT PK_demo PRIMARY KEY CLUSTERED (id ASC) )

Next add some data:

INSERT INTO DEMO (id, fname, lname, AddressLine1, City, State, Zip, ModifiedDate)
VALUES (1,‘Joe’,’Smith’,’500 E Brook’,’Dallas’,’TX’,’75244′,’2017-09-25 00:00:00.000′),
(2,‘Sarah’,’Kemp’,’101 Walhbro’,’Plano’,’TX’,’75231′,’2017-09-25 00:00:00.000′)

Add a simple view:

CREATE VIEW dbo.vDemoInfo
AS
SELECT d.*
FROM demo d with (NOLOCK)
GO

Query the View:

SELECT * FROM vDemoInfo

Note that everything looks good.

image

Next Alter the table (Add AddressLine2 in the middle):

image

Finally, Compare the base table and view

SELECT * FROM demo
SELECT * FROM vDemoInfo

The view is completely broken!

image

Summary

My conversion error wasn’t because of data or because the data types or code was bad. It was because the columns shifted in the view and what should have been a date was now an alpha string. Rebuilding the view resolved the issue.

I feel like there are two solid take-aways.

  1. Never use SELECT * in product code.
  2. If you alter a table be sure to check the views that reference it because they may be broken.

4 thoughts on “Don’t forget to drop your views

  1. another suggestion: never use the table designer to alter / add columns; use always explicit ALTER statements instead.

    In this case the column would have been added to the end of the table. It would still be missing in the view, but at least the other columns would be returned

    Like

  2. This is not an argument against
    – select * or
    – the table designer.
    It simply says that a view may brak if you change the underlying table. I sometimes had the impression that this may also occur when you change a view and this view has some other view sitting on top of it – possibly depending on wether the query analzer is substituting the text of the base view (good case) or takes it as a whole (bad case).

    Like

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