Friday, June 23, 2017

View defined as SELECT * FROM Another View

This has been covered previously, but is so insidious that it deserves more attention. Take a look at the code below. Fairly straightforward, right?

CREATE VIEW V1 AS SELECT [object_id] FROM sys.objects
GO

CREATE VIEW V2 AS SELECT * FROM V1
GO

SELECT TOP 10 * FROM V2
GO


Now let's modify the definition of view V1, such that the column definition includes [name]:

ALTER VIEW V1 AS SELECT [name], [object_id] FROM sys.objects
GO

SELECT TOP 10 * FROM V2
GO


See how the definition of V2 has not changed, even as the data that it contains has? No error has occured, which would have alerted us to the issue. This illustrates one of the biggest risks of using "SELECT *" in SQL code.

No comments:

Post a Comment