5

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?

2
  • If you change the structure of any tables in the underlying view, select * may break any applications that rely on the columns being in a specific order etc. It's generally accepted that doing select * anywhere, not just in view definitions, is bad practice. – Philᵀᴹ Oct 8 '15 at 12:36
  • 2
    Actually, that's the point of doing it in this case - if the underlying view changes, the changes would be picked up in the proxy. The application would need to change in either case. – Metaphor Oct 8 '15 at 12:43
14

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.

4
  • This is a view of a view in a different database. The reason for select * is so that both view definitions don't need to be updated in case of a change. Eventually, the original view will be removed and it's definition moved into the proxy. – Metaphor Oct 8 '15 at 12:43
  • 3
    @Metaphor I still argue that saving 4 seconds of typing is not worth the potential disaster depending on the type of change that is made. Did you try the repro? If you make a change to the underlying view (or its underlying table), you should make changes to all of the dependent objects in a planned manner. Period. How many changes are you really going to make between now and "eventually"? How much extra work is maintaining one extra column list really going to create? You asked for reasons not to do it, I'm giving you reasons not to do it. If you're just going to ignore them, why did you ask? – Aaron Bertrand Oct 8 '15 at 12:47
  • I'm not ignoring it, I'm making an argument. Your opinion is very much appreciated. – Metaphor Oct 8 '15 at 13:42
  • dragging the columns folder to the window is a huge timesaver! Thanks! – Drewdin Aug 20 '18 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.