SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Confessions of a Microsoft Addict

Daniel Janik has been supporting SQL Server for 18 years as a DBA, developer, architect, and consultant. He spent six years at Microsoft Corporation supporting SQL Server as a Senior Premier Field Engineer (PFE) where he supported over 287 different clients with both reactive and proactive database needs. Daniel has spoken at several SQL Saturday events across the US and Caribbean and regularly speaks at PASS local chapters.

Comments

Leave a comment on the original post [sqltechblog.com, opens in a new window]

Loading comments...