question

Senn-4458 avatar image
0 Votes"
Senn-4458 asked SeeyaXi-msft commented

Query Optimization (hash index, B+ tree)

Hi everyone,

I know this place is not to ask question like this. but I have one question about one topic. As you can see for e.g. its mentioned that age uses hash index. But in the a) part you see that make calculation with B+ tree. Why? I don't get it I did the calculation with hash index. I thought for equality search you use hash index`? Other question where does this purple calculation come from why is it needed?

109768-image.png
109686-image.png
109788-image.png


sql-server-general
image.png (91.9 KiB)
image.png (38.4 KiB)
image.png (116.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Senn-4458 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it dosn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered ErlandSommarskog commented

Hi @Senn-4458,

In the case of clustered indexes, the actual data is stored in the leaf node. It's not a pointer like a nonclustered index.
109842-index.png
For the purple section, you can understand it from the picture (which shows the data structure diagrams for both clustered and non-clustered indexes).

Please refer to this: Difference between hash index and B+ tree index (difference between hash index and B+ tree index), which may help you.
In most cases, choosing the B+ tree index directly can achieve stable and good query speed without using the Hash index.

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


index.png (125.9 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your answer. But why did they mention in the task e.g. its hash index but calculated the B+ tree index? Because its better? So I don't have to care about the hash index?

The 2000 does it come from 100 byte * 20 data entries?

0 Votes 0 ·

SQL Server has hash indexes - but only for so-called memory-optimized table, which is a niche part of the product. For normal disk-based tables, SQL Server only has B-tree indexes.

So, yeah, don't expect too many good answers on hash indexes here.

0 Votes 0 ·
SeeyaXi-msft avatar image
1 Vote"
SeeyaXi-msft answered ErlandSommarskog commented

Hi @Senn-4458,

But why did they mention in the task e.g. its hash index but calculated the B+ tree index?

Statistics are an important part of the entire process as they help the query optimizer to make the best guesses when accessing data. These statistics include information about columns like: estimated number of rows, the density of pages on disk, available indexes to use, etc. that the query optimizer uses to generate query plans. It’s essential to keep these statistics up-to-date as the query optimizer will use them to enforce query plans. But this is something that SQL Server does automatically and it also does an excellent job with default settings too, so you don’t have to worry about this except to know that it’s an important aspect.
Therefore, it can be said that it chooses the best calculation method according to the statistical information.

The 2000 does it come from 100 byte * 20 data entries?

Yes. Please refer to this: https://my.vanderbilt.edu/cs265/files/2012/11/BplusTreeBasics3.pdf

Best regards,
Seeya
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes. Please refer to this: https://my.vanderbilt.edu/cs265/files/2012/11/BplusTreeBasics3.pdf

A very good link you found there, good work! I hope it is useful for Senn.

0 Votes 0 ·