Click below for Step by Step and Interview Q & A (dotnetinter) wrote,
Click below for Step by Step and Interview Q & A

SQL Server interview questions with answers :- What are Indexes in Sql Server?

We are thankful to to provide this SQL Server interview question with answer.

Before I go and answer this question, answer me one thing. Let’s say you have a book and you want to navigate to 6th lesson. What will be your first step? Will you start turning your page one by one till you reach 6th lesson. If yes then you are awesome…no need to learn anything else. If your answer is No then what will you do. I wise person will first use the index of the book and get the page number of lesson and navigate accordingly.

sql s

Sql server does the same task. The only goal of using indexes in sql server is increasing search performance. (In select queries)

How search normally performed in sql server?

It is done in sequential manner. It means if you are looking for 10th row you have to manually browse every previous row, asking everyone the same question ”Are you row 10” and everyone reply “No”.
This is called as Table scan.

Anyone can easily say this will affect performance.

How indexes improves search?

Indexes create B-Tree structure or Balanced tree structure internally.

What is B-Tree?

  • In B-Tree there will be a root node at the top, leaf node at the bottom and intermediate node at the middle.

  • Root node and Intermediate node contain Index rows.Index Row contains 1) Key value 2) pointer to another intermediate row or leaf node.

  • Leaf node will point to actual data (applicable only for clustered Index)

sql s2
How nodes in the B-Tree organized?

Root node value will have some value. It will point to 2 intermediate nodes. Left node will contain value which will be less than root node value and right node will contain value which will greater than route node.
Now this will applicable for all intermediate nodes. Means every intermediate will have two child’s which may be leaf node or may be again intermediate nodes. If in case they are intermediate nodes, it will be placed in such that, one with less value will be placed at left position and one with higher value will be placed at right position.

How B-Tree makes search faster?

While searching when B-Tree is used lots of records got skipped, because our data will be either at left side or at right side. Ultimately performance get improved.
This kind of searching is called as Index scan.

Also see the following SQL Server interview questions and Answers video on Can SQL Server views be updated.

Tags: clustered index interview question, csharp interview questions, information technology questions, non-clustered index interview question, programming interview questions, sql server interview question on indexes

  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded