Hash Indexes

Question 1

Where are hash indexes used?

Answer 1

Memory Optimized OLTP Tables

Question 2

What is a hash index?

Answer 2

An array of N buckets or slots, each one containing a pointer to a row.

Question 3

How does a hash index work?

Answer 3

It uses a hash function in which given a key and the number of buckets will map the key to the corresponding bucket of the hash index.

Question 4

How does Microsoft handle hash collisions (when a hash function returns the same for two keys)?

Answer 4

Using chaining with linked lists.

Question 5

How do hash indexes handle deletes?

Answer 5

It sets the end timestamp of the updated row to the current time (rendering it inactive) and adds a new row at the end of the chain with the last row having the index pointer to the new row.

Question 6

How does a hash index find a record it is searching for?

Answer 6

• Find bucket the record is in
• Check the first row for an end timestamp and key match
  
  • If key match and no end timestamp, then return
• If not, go to the pointer value to look at next record
• Repeat until match is found or end is reached.

Question 7

What is the syntax for creating a hash index?

Answer 7

CREATE TABLE dbo.StringTable_Hash
(
PersonID nvarchar(50) NOT NULL ,
CONSTRAINT PK_StringTable_Hash PRIMARY KEY NONCLUSTERED HASH (PersonID)
WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

Question 8

How many buckets should you create?

Answer 8

Create the buckets so that the most you can have based on the number of keys fills to 80%.

Question 9

When should you use a nonclustered index for a memory optimized table?

Answer 9

• Queries have an ORDER BY clause on the indexed column.
• Queries where only the leading column(s) of a multi-column index is tested.
• Queries test the indexed column by use of a WHERE clause with:
  
  • An inequality: WHERE StatusCode != ‘Done’
  • A value range scan: WHERE Quantity >= 100

Question 10

When should you use a hash index for a memory optimized table?

Answer 10

A hash index is preferable over a nonclustered index when queries use equality predicates, and the WHERE clause maps to all index key columns

Question 11

If a multi-column exists and there’s a selection on only one of the columns, which type of index would handle that better?

Answer 11

The nonclustered index. Hash index can’t handle that.

As long as it is the FIRST column in the index list.

If the select has all columns in the where statement, then the hash index is better.

Question 12

Does a memory-optimized hash handle Index Scan, retrieve all table rows.

Answer 12

Yes

Question 13

Does a memory-optimized hash handle Index seek on equality predicates (=).

Answer 13

Yes

Full key is required.

Question 14

Does a memory-optimized hash handle Index seek on inequality and range predicates
(>, =, BETWEEN).

Answer 14

No

Results in an index scan.

Question 15

Does a memory-optimized hash handle Retrieve rows in a sort order that matches the index definition.

Answer 15

No

Question 16

Does a memory-optimized hash handle Retrieve rows in a sort-order that matches the reverse of the index definition.

Answer 16

No