sql 2005 强制使用执行计划 T—SQl

select * from tt t inner loop join ss s with(nolock) on s.c=t.c

 

 使用 nested join

select * from tt t inner merge join ss s with(nolock) on s.c=t.c

 

 使用 merge join

 

select * from tt t inner hash join ss s with(nolock) on s.c=t.c

 

使用  hash jion

 

nolock 不允许锁 

 

 

 

 

Microsoft SQL Server sometimes uses hash and merge joins when querying large tables when uncomplicated nested loop joins would result in better performance and less server impact. In many such cases, query times go from many milliseconds to many seconds because hash table joins require that large amounts of data be processed and temporarily stored, and merge joins require sorting and then processing similarly large amounts of data.

 

This is fine for one-time administrative "fact finding" queries where you don't have or want the indices needed to optimize the query, and you're willing to wait the seconds or minutes it takes to get results.

For day-in-and-day-out application queries, however, you don't want your database engine to be hashing or sorting hundreds of thousands or millions of rows, especially when the end result is only a small number of rows.