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? 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 are the differences in how a hash index and a memory optimized nonclustered index handle range queries and equality lookups? 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 is a hash index, and how is it different from the more common B-tree index used for regular disk based tables?

Beginner

Answer

A hash index stores a computed hash value for each row's indexed column, allowing extremely fast equality lookups, and is specifically the type of index used with memory optimized tables in SQL Server, unlike the B-tree structure used by standard indexes on disk based tables, which also supports efficient range queries that a hash index does not.
CREATE TABLE SessionData (
  SessionId INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
  UserId INT
) WITH (MEMORY_OPTIMIZED = ON);
Real-world example A high traffic session tracking system uses a hash index on a memory optimized table to achieve extremely fast lookups by exact session id, since the application never needs to search for a range of session ids.

Common follow-ups: Why can't a hash index efficiently support a range query like BETWEEN?;How do you choose an appropriate bucket count for a hash index?

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