SQL Server2014 Hash Index principle detailed _mssql

When a Key-value key value pair is passed to a hash function, after the hash function is computed, the Key-value key value pair is placed in the appropriate hash buckets (hash bucket) according to the result.

Give me a chestnut.

We assume that the 10 modulo (% 10) is the hash function. If the key of the Key-value is 1525, passed to the hash function, then 1525 will be stored in the fifth bucket

Since 5 as 1525% 10 = 5.

Similarly, 537 will be stored in the seventh bucket, 2982 will be stored in the second bucket, and so on

Similarly, hash index columns are passed to the hash function to match (similar to the HashMap map operation in Java), and after the match is successful,

The index column is stored in the table that matches the hash bucket, which has the actual data row pointer, and then finds the corresponding data row based on the actual data row pointer.

Generally, to find a row of data or to work with a WHERE clause, the SQL Server engine needs to do the following

1. Generate the appropriate hash function according to the parameters in the Where condition

2, the index column to match, matching to the corresponding hash bucket, find the corresponding hash bucket means also found the corresponding data row pointer (row pointer)

3. Read the data

A hash index is simpler than a B-tree index because it does not need to traverse the B-tree, so the access speed is faster

Examples of hash functions and corresponding grammars

CREATE TABLE dbo. Hk_tbl
 (
  [ID] INT IDENTITY (1, 1) not
    NULL
    PRIMARY KEY nonclustered HASH with (Bucket_count = 100000), 
   [data] Char (COLLATE) latin1_general_100_bin2
      null,
  [DT] datetime NOT NULL,) with
 (
   MEMORY _optimized =
   on,
   durability =
   schema_and_data);

In SQL Server 2014, the memory tuning table cannot be hashed after it is created, but the hash index is added after the support table is created in SQL Server 2016, but

Adding a hash index is an offline operation.

Number of bucket for hash index

(Bucket_count = 100000) defines the number of BUCKET that a hash index can use, the BUCKET is fixed and the number of BUCKET specified by the user,

Instead of executing the query, the SQL Server determines the number of bucket generated. The number of bucket is always 2 of the rounding (1024, 2048, 4096 etc ...)

SQL Server2014 Hash index is actually similar to MySQL's adaptive hash indexing principle, is to get rid of B-tree constraints, so that the search efficiency faster

How does a relational database work this article also has a description of the principle of hash join, you can see