SQL Tips by Namwar Rizvi

June 4, 2007

Hash Join: The clear indicator of Index requirement

Many people ask me What is Hash join? When SQL Server uses HASH join algorithm? The answer is, if you do not have index on either column of a join statement then SQL Server has to scan both the tables to match the rows. Is it really feasible? Is there can be anything better than this?
Yes, there is one algorithm which is Hash algorithm. If you recall from your school days of Data Structure course you will easily understand the importance of data hashing here. The idea is simple:
1. Take a sample set of data values from the column participating in Join (Each value represents a data bucket)
2. Distribute the rows in respective buckets according to their values (Rows will get the bucket number by applying the hash function on the value and the result will be the bucket number)
3. Check the existence of any data value by applying the hash on the value, getting the bucket number as a result and searching on that bucket only.

It looks complex but it is much more efficient than a simple table scan of a huge table.

Always remember, the quickest algorithm for searching is the B-Tree which is the default algorithm for SQL Server but it is applicable on indexed columns or SQL Server builds it dynamically sometimes for very small tables.

Please note: Whenever you see a Hash join operator in your query plan than most of the time it is a clear indicator of a Index requirement on that column but beware, if it is an ad hoc query and you are not willing to run this query on production then do not make excessive indexes just because of Hash Join operator.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: