Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the principle of hash index in SQL Server. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

When a key-value key-value pair is passed to a hash function, after the calculation of the hash function, the key-value key-value pair will be put in the appropriate hash buckets (hash bucket) according to the result.

Take a chestnut.

Let's assume that the module (% 10) for 10 is a hash function. If the key of the key-value key-value pair is 1525 and 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 in the second bucket, and so on

Similarly, in hash index, the hash index column is passed to the hash function for matching (similar to the Map operation of HashMap in java). After the match is successful,

The index column will be stored in the table in the matching hash bucket, where there will be the actual data row pointer, and then look up the corresponding data row according to the actual data row pointer.

In a nutshell, to find a row of data or process a where clause, the SQL Server engine needs to do the following

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

2. Match the index column to the corresponding hash bucket. Finding the corresponding hash bucket means that the corresponding data row pointer (row pointer) is also found.

3. Read data

The hash index is simpler than the 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 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, no hash indexes can be added after memory-optimized tables have been created, but in SQL Server 2016, hash indexes can be added after table creation, but

Adding a hash index is an offline operation.

Number of Bucket indexed by hash

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

Instead of the number of Bucket generated by SQL Server when the query is executed. The number of BUCKET is always rounded to the power of 2 (1024, 2048, 4096 etc..)

SQL Server2014's hash indexing is actually similar to MySQL's adaptive hash indexing principle, which is to get rid of the shackles of B-tree and make the search more efficient.

So much for sharing the principles of hash indexing in SQL Server. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.