Hash Indexes on "In memory" tables are best suited for equality searches. Reason is obvious, as they are based on hash algorithm which doesn't sort data, hash indexes aren't useful for sorting or non equality searches ( Refer here). But, hashing being a fast and a effective technique, hash indexes are perhaps the best bet for handling equality searches ( where conditions using "=" ). Note that, equality searches are by far the most common filter condition you would see in queries. So, it is one of those things Microsoft has done to address the obvious with best possible solution ignoring the not so common problems 🙂 Besides equality filters other areas where hash indexes can be useful are
Group by clause:
Consider the following query
SELECT count(*),data
FROM [HK_tbl]
GROUP BY Data
Query plan indicates a index scan but no additional sorting or grouping required, as even though data is not sorted by hash index, it is grouped / categorized and hence it is very effective for processing "group by" queries.
Group by clause:
Consider the following query
SELECT count(*),data
FROM [HK_tbl]
GROUP BY Data
Query plan indicates a index scan but no additional sorting or grouping required, as even though data is not sorted by hash index, it is grouped / categorized and hence it is very effective for processing "group by" queries.
Inner Join:
HK_tbl2 is another "in memory" table with hash index on data column.
SELECT HK_tbl.data,HK_tbl2.dt
FROM HK_tbl
INNER JOIN HK_tbl2
ON HK_tbl.data = HK_tbl2.data
Outer table is scanned as there is no filtering condition while the inner table makes use of the "Hash Index" . Query plan shown below
Left Outer Join:
SELECT HK_tbl.data,HK_tbl2.dt
FROM HK_tbl
LEFT OUTER JOIN HK_tbl2
ON HK_tbl.data = HK_tbl2.data
Semi equijoin condition like left outer join also benefits from hash index
