Difference Between Following Queries..

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 2000 Forums

Transact-SQL (2000)

Difference Between Following Queries..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-12 : 06:51:55

Hi All,

I was reading one of the Article from Kalen Delaney and i came accross the following queries.

I would like to know the basic difference between them and does they have any performance gain over each other..


Declare @Tbl1 Table 
(
   i Int,
   j varchar(100)
)

Declare @Tbl2 Table 
(
  i int
)

Insert @Tbl1 
Select 1,'A' Union All 
Select 2,'B' Union All 
Select 3,'C' Union All 
Select 4,'D' 

Insert @Tbl2 
Select 1 Union All 
Select 4

Select a.i,b.j From @Tbl2 a Inner Loop Join @Tbl1 b 
On a.i = b.i

Select a.i,b.j From @Tbl2 a Inner Hash Join @Tbl1 b 
On a.i = b.i

Select a.i,b.j From @Tbl2 a Inner Join @Tbl1 b 
On a.i = b.i

Chirag

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-09-12 : 09:38:21

Check out http://www.sql-server-performance.com/mb_sql_server_joins.asp for more information and advice on the various types of joins, also see books on line for HINTS for joins

Basically LOOP, HASH and MERGE joins are different ways that SQL Server goes about comparing the data between two tables to form your result set. Depending on the amount of data you have you might want a particular thing to happen or NOT to happen so you can HINT to the optimizer to do it your way instead of just taking whatever it might give you.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-12 : 10:03:20

Thanks i will go through link..

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources