Share via

Query Optimization (hash index, B+ tree)

Senn 41 Reputation points
Jun 28, 2021, 7:40 AM

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,075 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,471 Reputation points
    Jun 29, 2021, 3:24 AM

    Hi @Senn ,

    > 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 person found this answer helpful.

  2. Seeya Xi-MSFT 16,471 Reputation points
    Jun 28, 2021, 8:58 AM

    Hi @Senn ,

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.