SQL Server Interview Questions on Indexes - Part 2



What are the disadvantages of an Index?
There are 2 disadvantages of an Index
1. Increased Disk Space
2. Insert, Update and Delete statements could be slow. In short, all DML statements could be slow.


Disk Space: Indexes are stored on the disk, and the amount of space required will depend on the size of the table, and the number and types of columns used in the index. Disk space is generally cheap enough to trade for application performance, particularly when a database serves a large number of users.


Insert, Update and Delete statements could be slow: Another downside to using an index is the performance implication on data modification statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the database needs to update all of the indexes where data has changed. Indexing can help the database during data modification statements by allowing the database to quickly locate the records to modify, however, providing too many indexes to update can actually hurt the performance of data modifications. This leads to a delicate balancing act when tuning the database for performance.


What are the 2 types of Indexes in SQL Server?
1. Clustered Index
2. Non Clustered Index


How many Clustered and Non Clustered Indexes can you have per table?
Clustered Index - Only one Clustered Index per table. A clustered index contains all of the data for a table in the index, sorted by the index key. Phone Book is an example for Clustered Index.
Non Clustered Index - You can have multiple Non Clustered Indexes per table. Index at the back of a book is an example for Non Clustered Index.


Which Index is faster, Clustered or Non Clustered Index?
Clustered Index is slightly faster than Non Clustered Index. This is because, when a Non Clustered Index is used there is an extra look up from the Non Clustered Index to the table, to fetch the actual rows.



When is it usually better to create a unique nonclustered index on the primary key column?
Sometimes it is better to use a unique nonclustered index on the primary key column, and place the clustered index on a column used by more queries. For example, if the majority of searches are for the price of a product instead of the primary key of a product, the clustered index could be more effective if used on the price field.


What is a Composite Index in SQL Server?
or
What is the advantage of using a Composite Index in SQL Server?
or
What is Covering Query?
A composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes.
If all of the information for a query can be retrieved from an Index. A clustered index, if selected for use by the query optimizer, always covers a query, since it contains all of the data in a table.


By creating a composite indexes, we can have covering queries.

20 comments:

  1. Thank you so very much for this blogsite. It's good to have all the information in one place.
    If you set up a donation cart, I would be happy to contribute from the first paycheck of my next job; hopefully soon with your help.

    I did not quite understand the Covering Query, can you please give an example?

    Keep up your good work
    From an Out Of Practice Developer

    ReplyDelete
    Replies
    1. Covering query pulls some fields from db. If each of this field has index implemented this is called covering query.

      Example

      SELECT EmployeeID, Salary FROM Employees
      ORDER BY Salary

      If we have for example clusterd index on EmployeeID and non-clustered one on Salary then this is covering query

      Delete
    2. can anyone explain schema binding with real-time scenarios?

      Delete
  2. It's very good collection. Good concepts with very good explanation. Keep it up.

    ReplyDelete
  3. hi........dis iz sanjay when i came on this site nd nvr expect..i will get qsns lke dat
    thnk you very mch............keep it up

    ReplyDelete
  4. very goo collection.

    ReplyDelete
  5. thankk you from bottom of my heart.....thank u so muchhh :)

    ReplyDelete
  6. Thanks a lot it is really helpful ...nice collection...thanks for your hard work .

    ReplyDelete
  7. It's really worthy and easy way of explanation.
    clearing most of the doubts of SQL indexes.

    Requesting for more explanation on Covering query...........

    ReplyDelete
  8. Can u explain Clustered and Non Clustered index with an example? Whats the difference with these two?

    ReplyDelete
  9. how to overcome the disadvantages of using index?

    ReplyDelete
  10. Thanks a lot for such a good collection.
    Can you please explain with example on covering query

    ReplyDelete
  11. Thank you for the good questions.
    It will help us to understand better if you can explain these with examples like the C# tutorial.

    ReplyDelete
  12. Thanks a lot....for the great information
    God bless you and keep it up.

    ReplyDelete
  13. Awesome explanation. Hats off Venkat Sir. Its because of you and you SQL Tutorials i have learnt a lot and this helped me in my interviews.

    Thanks a lot sir....

    ReplyDelete
  14. Excellent explanation with simple words and Concepts are understood thoroughly. Your videos are very helpful. Please keep posting.

    ReplyDelete
  15. very very excellent venkat sir.

    ReplyDelete
  16. Thanks a lot .....will u please explain difference between SQL and T_SQL

    ReplyDelete
  17. How to create a non-clustered index?

    ReplyDelete
  18. This is Manoj Sharma

    "Index at the back of a book is an example for Non Clustered Index." I took this line from above answer.
    So as i know Index at back of a book contains all keywords and page no. but Sql Index contains row address only for column values on which index is created. Please tell me if i am wrong,then explain how ?

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us