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.

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

Finally, Compare the base table and view

SELECT * FROM demo
SELECT * FROM vDemoInfo

The view is completely broken!

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.