The new index type introduced by sql server 2014 is called hash index. Before introducing hash index, you must introduce the hash function so that everyone will understand the principle of hash index more.

When a key-value pair is passed to a hash function,After the calculation of the hash function,According to the results, key-value pairs will be placed in the appropriate hash buckets.

Give a chestnut

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

Because 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, in the hash index, the hash index column will be passed to the hash function for matching (similar to the map operation of hashmap in Java). After the matching is successful,

The index columns will be stored in a table in the matching hash bucket.There will be actual data row pointers in this table,Then find the corresponding data line according to the actual data line pointer.

Generally speaking,To find a row of data or process a where clause, the SQL server engine needs to do the following things

1.Generate a suitable hash function according to the parameters in the where condition

2, the index columns are matched,Matches the corresponding hash bucket. Finding the corresponding hash bucket means that the corresponding data row pointer is also found.

3.Read the data

A hash index is simpler than a b-tree index.Because it does not need to traverse the b-tree, the access speed will be faster

Examples of hash functions and corresponding syntax

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 =

In SQL Server 2014, hash indexes cannot be added after the memory optimized table is created.However, in SQL Server 2016, adding a hash index is supported after the table is created.but

Adding a hash index is an offline operation.

Hash index buckets

(bucket_count=100000) defines the number of buckets that the hash index can use. This bucket is fixed and the number of buckets specified by the user.

Rather than the number of buckets generated by the sql server when the query is executed. The number of buckets is always rounded to the power of two (1024, 2048, 4096 etc ..)

The hash index of sql server2014 is actually similar to the principle of MySQL's adaptive hash index.All to get rid of the constraints of the b-tree,Make search faster

how does a relational database work This article also describes the principle of hash join, you can take a look

  • Previous C ++ character array input and output and use of string end markers explained
  • Next Summary of common file operations in JSP