SQL Server – Issues with Using SELECT * in View Definition

sql serversql server 2014view

I am creating a number of proxy views for views in a separate database. To avoid defining the view in two places, I want to use SELECT * to create the proxy view.

I've searched for reasons not to do this and didn't find any. Anyone have an opinion on using SELECT * for a view definition?

Best Answer

Yes, there are reasons to not use SELECT * in a view. The most important is that SQL Server caches the metadata of a view's output, and it doesn't magically update if underlying objects change. Here's a quick example to demonstrate:

USE tempdb;
GO
-- simple table with two int columns
CREATE TABLE dbo.x(a INT, b INT);
GO
INSERT dbo.x(a,b) VALUES(1,2);
GO

-- simple view using SELECT *
CREATE VIEW dbo.v_x
AS
  SELECT * FROM dbo.x;
GO

-- view will not be updated to see these changes:
EXEC sys.sp_rename N'dbo.x.b', N'c', N'COLUMN';
ALTER TABLE dbo.x ADD b DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
GO

-- view shows wrong data
SELECT * FROM dbo.x;
SELECT * FROM dbo.v_x;
GO

You need to force SQL Server to refresh its view of the view.

EXEC sys.sp_refreshview @viewname = N'dbo.v_x';
GO

-- now view is correct
SELECT * FROM dbo.v_x;
GO

Now why do you want to make an argument for using SELECT * in a view? Because you don't want to have to type the column names a second time? Did you know that you can easily generate the list of columns from Object Explorer by right-clicking and saying Script View/Table As > SELECT To > clipboard? Or by dragging the columns folder onto the query window?

enter image description here

Just don't do it. What you save in typing (and the time you've already wasted debating this) isn't worth the risk and doesn't justify having some exception where you make it seem like SELECT * is okay. It's a bad practice for a multitude of reasons, and going out of your way to make exceptions is inconsistent and can often encourage the wrong thing to junior developers.

In fact I try to remember to create all of my views WITH SCHEMABINDING so that changing them later is a pain. I don't want to be able to change views willy-nilly precisely because that's a surefire way to break something.

Related Question