Generally what is a better practice.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generally what is a better practice.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-25 : 11:14:10

I need to retrieve all the records in one table, and the count of corresponding records.

THere are immidiatly 2 ways to do this



Select *,
(Select count(*) from T_Relation aa where aa.RelationID = a.RelationID)
from
T_Main a



Select a.f1,a.f2,a.f3,a.f4,a.f5,a.RelationID,Count(*)
from
T_Main a
Inner join
T_Relation b
on a.RelationID = b.RelationID
group by a.f1,a.f2,a.f3,a.f4,a.f5,a.RelationID

Under the assumption that table main has proper index's and 50,000 records and table Relation has 500,000 records and proper index's, which is generally a better practice.

I am asking because I need to write about 15 similar queries, and want to keep them all the same format.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 11:28:55

I think the join method is better if you've the proper indexes on the columns.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-25 : 12:38:03

I also prefer the join method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources