Select All Obsolete


When prototyping it is very convenient to select all columns from a table or a view. so we do something like:
select * from dbo.sviw_Deploy_Version

All is well until this select statement ends up in production as a view. If someone alters [sviw_Deploy_Version] you must call sp_refreshview. You then call it in development and all is fine again. Until it is not fine in production after you update it, since no one called refresh views.

We tried really hard tracking what needs to be refreshed and refreshing it automatically. It proved neraly impossible to make it work 100% of the time. To solve this issue we just need to stop using select * in our views.

  • when you drop / rename a column from a table you have to update all referencing views that actually include old column
  • when you add a column you most probably need to add it only to some single view not all the views referencing the table
So when not using select * in your views you will actually end up with views that actually include only what is needed.

To make developer life easier Database Manager includes festures like expand column list and get referencing objects. So when you want to create a new view you would most probably like to include all the columns using Alt + C keyboard shortcut.

If you need to be a little bit more strict and specify table alias you can still do it using same Alt + C keyboard shortcut.

If you forgot key binding or you are more mouse person then a keyboard person, you can use database manager quick fix functionality (enabled by monaco editor). Hover your mouse over the suggestion and click it.

You can also invoke quick fix using your keyboard by pressing Ctrl + .

As mentioned before if you would add additional column to [sviw_Deploy_Version] your referencing view does not become broken since you do not specify it in select columns list. But if you would remove the column then you would need to check all the objects that actually use this object and posibly remove the used column ahead of time. To get referencing objects use Alt + A keyboard shortcut.

Hope this helps a bit in your development lives.


Related articles

Placeholder "LocalizeWeb2016" failed