All 7 Hash Indexes & Hash Join Operations questions
More in Hash Indexes & Hash Join Operations
What is a hash join, and how does SQL Server use it to combine two tables? What is a hash index, and how is it different from the more common B-tree index used for regular disk based tables? How does the BUCKET_COUNT setting affect the performance of a hash index on a memory optimized table? In what situations does SQL Server's query optimizer typically prefer a hash join over a nested loop join or a merge join? What happens when a hash join does not have enough memory available to build its hash table entirely in memory, and how does this affect performance? How would you troubleshoot a query that is unexpectedly using a slow hash join when a much faster index seek and nested loop join should be possible?
Advertisement
SQL Server › Hash Indexes & Hash Join Operations

What are the differences in how a hash index and a memory optimized nonclustered index handle range queries and equality lookups?

Intermediate

Answer

A hash index is optimized purely for exact equality lookups and performs poorly or not at all for range based queries like finding all values greater than a certain number, while a memory optimized nonclustered index, which uses a different internal structure, can efficiently support both equality and range based queries, making it the better choice when your query patterns include range filtering or sorting.
-- Use a nonclustered index instead of hash
-- for range query support
CREATE TABLE OrderLog (
  OrderId INT NOT NULL,
  OrderDate DATETIME2 NOT NULL INDEX IX_OrderDate NONCLUSTERED
) WITH (MEMORY_OPTIMIZED = ON);
Real-world example A logging system originally used a hash index on its timestamp column, but switched to a memory optimized nonclustered index after realizing most of their queries needed to filter by a date range rather than an exact timestamp match.

Common follow-ups: Why does a hash index perform poorly for range queries specifically?;How do you decide between a hash index and a nonclustered index on a memory optimized table?

In-Memory OLTP (Memory-Optimized Tables);Indexes
All 7 Hash Indexes & Hash Join Operations questions