?

Log in

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

Buy 600+ Real Time Interview Videos

Call +91-22-66752917 or mail us on questpond@questpond.com for more details on C#, .NET, ASP.NET, SQL Server interview question videos.

SQL Server interview question videos

Latest Month

January 2017
S M T W T F S
1234567
891011121314
15161718192021
22232425262728
293031    

Tags

Powered by LiveJournal.com