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 = schema_and_data);
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
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- python 3x - typeerror: 'method' object is not subscriptable
- i want to call a child component method from a parent in vuejs
- xcode - pod install [!] no `podfile 'found in the project directory
- dart - flutter: the instance member'stars' can't be accessed in an initializer error