which is better

[kodaksmile]

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?

 

[gmmastros]

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

 

[markros]

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

 

[NastiaMurzin]

select * from dbo.tableschema.tablename1, dbo.tableschema.tablename2;
That's easier.

 

[SQLSister]

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

 

[danvlas]

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.




Daniel Vlas
Systems Consultant