Flashcards in Hash Indexes Deck (16)
Where are hash indexes used?
Memory Optimized OLTP Tables
What is a hash index?
An array of N buckets or slots, each one containing a pointer to a row.
How does a hash index work?
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.
How does Microsoft handle hash collisions (when a hash function returns the same for two keys)?
Using chaining with linked lists.
How do hash indexes handle deletes?
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.
How does a hash index find a record it is searching for?
* 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.
What is the syntax for creating a hash index?
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)
How many buckets should you create?
Create the buckets so that the most you can have based on the number of keys fills to 80%.
When should you use a nonclustered index for a memory optimized table?
* 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
When should you use a hash index for a memory optimized table?
A hash index is preferable over a nonclustered index when queries use equality predicates, and the WHERE clause maps to all index key columns
If a multi-column exists and there's a selection on only one of the columns, which type of index would handle that better?
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.
Does a memory-optimized hash handle Index Scan, retrieve all table rows.
Does a memory-optimized hash handle Index seek on equality predicates (=).
(Full key is required.)
Does a memory-optimized hash handle Index seek on inequality and range predicates
(>, =, BETWEEN).
(Results in an index scan.)
Does a memory-optimized hash handle Retrieve rows in a sort order that matches the index definition.