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.
- Never use SELECT * in product code.
- If you alter a table be sure to check the views that reference it because they may be broken.
SQL TECH BLOG 
[…] Daniel Janik shows what happens when you add a column to the middle of a table while a SELECT * view…: […]
LikeLike
SELECT * is never a good thing and I prefer views with SCHEMABINDING.
LikeLike
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
LikeLike
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).
LikeLike