Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

which is better

Status
Not open for further replies.
Joined
Jun 19, 2002
Messages
294
Location
US
I have a quick question... we need to pull data from 2 databases and combine it in a report. Is it better to create a view that is

select * from dbname.dbo.table
in the reporting instance
or just query the table directly using
.... from dbname.dbo.table
in the reporting instance?
 
Usually it makes no difference whether you use a (simple) view or the table directly because SQL Server will generate the same execution plan. In fact... if you want to know for sure which is faster/better, try comparing the execution plans.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
If you don't need to filter the result, then there should be no difference. If you need to use a parameter, then you can either use in-line table valued function or a query.

PluralSight Learning Library
 
select * from dbo.tableschema.tablename1, dbo.tableschema.tablename2;
That's easier.
 
NastiaMurzin, Not only is that answer wrong, it is dangerous. What you have there is a cross join which will match each record of table1 to each record of table2. So if table 1 had ten records and table 2 had 20, you might expect to get 20 back, but the cross join woul return 200. Now start thinking about what it would do if each table had a million records to see whay this is a problem.

This is a prime example of why the implicit join syntax (which is 18 years out-of-date BTW) should never be used. Accidental cross joins are too easy to get.

Further select * is a poor practice as well espcially when you have a join. You returning the same value in at least two columns (the join columns, assuming you didn't do a cross join) and that is a waste of both server and network resources. I would never allow this kind of junk programming to be put on my production machines.

"NOTHING is more important in a database than integrity." ESquared
 
I'd suggest using a view because of security reasons.
I would never ever give any type of permissions directly on tables, not even Select.



[pipe]
Daniel Vlas
Systems Consultant

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top