View creation best practice question

[wbodger]

I see views created with select * from... and some with select xx.yy, xy.yy from... Is there a best practice when you really do want everything from said tables in your view? Is it bad form to use select * from tablename?

Thanks,
WB

 

[djj55]

I believe best practice is to use column names. If you use * then change the underlying table the view does not change until you execute a sp_refreshview.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[wbodger]

OK, that was my thought, I just wasn't sure because I figured either way if you made a change to the underlying table you would have to then touch the view. It seems that if you do SELECT * then you could just write a script to step thru dbs and run sp_refreshview, but again I don't know if there are other reasons aside from just bad practice (that I thought I remembered hearing sometime in the past) to not do that...

 

[gmmastros]

Most of the time, you will hear people say not to use * in a select because this will return all of the columns. Often times, there are more columns in a table than you really need for a particular operation/query. By using "Select *", and then ignoring certain columns, you are wasting bandwidth, memory, and you could also end up with a less efficient query plan.

The rules are a little different when you are talking about a view. The view itself doesn't store any data (unless you index it). If you select just a couple columns from the view, the query optimizer will substitute the query in the view to determine the optimal execution plan. So... "select *" in a view is not necessarily bad if you only select the columns you need from the view.

Of course, there is another downside. If you have too many views that call other views, that call other views, the nesting makes it prohibitively difficult for the query optimizer to generate an optimal execution plan. Please try to avoid nesting your views too deeply or you will notice performance problems. Personally, I try not to nest views more than 2 deep.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Agreed, I try to stop at two, there are a few here that go three or four, but not many. Thanks for the info!

wb