?

Log in

No account? Create an account

Previous Entry | Next Entry

We are thankful to questpond.com 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.

Copy right www.questpond.com

Tags

Latest Month

September 2017
S M T W T F S
     12
3456789
10111213141516
17181920212223
24252627282930
Powered by LiveJournal.com