When a "key value" is passed to hash function, depending upon the result hash function provides, the key value will be placed in corresponding hash buckets.
For example, let us assume modulo ten ( % 10 ) is the hash function. if key value 1525 is passed to hash function, then it will be placed in hash bucket 5 as 1525 % 10 = 5. Similarly 537 would map to bucket 7 and 2982 would map to hash bucket 2 and so on..
So, similarly, in hash indexes, the indexed column value is passed to hash function and depending upon the result, a pointer to the actual row is stored in the table are stored in hash buckets. So, to find a row or to process a where clause, all that sql engine needs to
do is
- Apply the hash function on the search parameter
- Go to the hash bucket where the row pointer is stored
- fetch the result.
Simplicity and no tree traversals makes it one of the fastest methods of access.
Sample hash function usage or syntax provided below.
CREATE TABLE dbo.HK_tbl
( [ID] Int identity(1,1) Not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
[Data] char(32) COLLATE Latin1_General_100_BIN2 null,
[dt] datetime not null,
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
As Indexes can't be added after table creation in "in memory tables" in SQL Server 2014, one has to state the index definition inline. SQL Server 2016 supports adding indexes after table creation, but it is still a offline operation.
Bucket count specifies number of buckets to be used for the index. Bucket count is fixed and is always rounded off to next power of 2 ( 1024, 2048, 4096 etc..)
In subsequent posts, we get into more details on hash indexes and its pros and cons, hash bucket count and many more.
