Get Free Job Alerts on your Mobile by SMS- Subscribe now
What is Index?
Indexes are database objects designed to improve query performance.
By applying indexes to one or more columns in table or views, we could see faster data retrieval from these tables.
Explain the structure of Index in SQL server?
An index is structured by the SQL Server Index Manager as a balanced tree (or Btree). A B-tree is similar to an upside-down tree, means with the root of the tree at the top, the leaf levels at the bottom, and intermediate levels in between.
Each object in the tree structure is a group of sorted index keys called an index page.
All search requests begin at the root of a B-tree and then move through the tree to the appropriate leaf level.
What are the different types of indexes in SQL Server?
There are two types of indexes
• Clustered index
• Non Clustered index
Both types of indexes are indexes are structured as B-Trees.
Explain the difference between clustered index and non clustered index?
- A clustered index contains table records in the leaf level of the B-tree.
- There can be only one clustered index on a table or view, because the clustered index key physically sorts the table or view.
Non Clustered index:
- A non clustered index contains a bookmark to the table records in the leaf level.
- If a clustered index exists on a table, a non clustered index uses it to facilitate data lookup.
- We could create 249 non clustered indexes on a single table.
Tags:basic sql server interview questions and answers, index in sql server, index interview questions, sql index, sql index table, sql index types, sql indexes, SQL Server, sql server interview questions, sql server interview questions and answers, sql server interview questions and answers for developers, sql server interview questions and answers for freshers, sql server interview questions and answers for testers