SQL Server Interview Questions & Answers on Indexes
This article is part of SQL Server interview questions & answers series. Today, i am covering SQL Server Interview Questions & Answers on Indexes. You can have a look at the last few series of interview questions and answers on different SQL Server topics. Read this article to learn SQL Server interview questions on indexes.
- SQL Server Interview Q&A on Alwayson Availability Group
- SQL Server DBA Interview Questions & Answers
- SQL Server Backup & Recovery Interview Questions & Answers
- SQL Server Architecture Interview Questions & Answers
- Index Rebuild vs Index Reorganize Operation
SQL Server Interview Questions & Answers on Indexes
Question – How data stores in Indexes?
Answer – Data stores in indexes or in tables on a series of 8 kb data pages. Indexes Data on these data pages are organized in a B-Tree structure that supports fast retrieval of the rows, based on their index key values. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. The leaf nodes of a nonclustered index is made up of index pages instead of data pages whereas in a clustered index, the leaf nodes contain the data pages.
Question – What is Fill Factor and what should be the perfect value for it?
Answer – A fill factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. Fill factor values 0 and 100 are the same in all respects. The fill-factor option is provided for fine-tuning index data storage and performance. Although, there is no defined value that we can say is perfect value for fill factor. You can set it to somewhere around 80% and monitor fragmentation over time. Then, you can tweak its value up or down depending on how fragmented the indexes get. Read more about fill factor and its best value.
Question – Explain Page Split and whether it is good for SQL Server or bad?
Answer – Whenever you update existing rows with the data that is bigger in size to save on their data page then Page Split operation occur to make space for this new update. Page split reduces performance so we can say page split is not at all a good thing for our database.
Question – How page split is bad for performance?
Answer – Page split is a resource intensive operation and causes fragmentation that leads to deficient performance in terms of increased I/O operations. We should take it seriously if there are frequent page splits occur and consider reducing its occurrences
Question – What is Difference between clustered and nonclustered Indexes?
Answer – Clustered indexes sort and store the data rows in the table or view based on their key values. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
Nonclustered indexes have the same B-tree structure as clustered indexes. The data rows of the underlying table are not sorted and stored in order based on their non clustered keys. The leaf layer of a nonclustered index is made up of index pages instead of data pages. You can create up to 999 nonclustered indexes on a table.
Question – Does indexes always improve database performance?
Answer – NO, sometimes indexes can reduce your database performance if you are running a bulk data upload or your application has a nature of frequent data Insert operations. Indexes are very useful if you are accessing or reading the data.
Question – What is filter index?
Answer – An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
Question – What is covering index?
Answer – A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index.
Question – Explain column store index?
Answer – An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.
Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries.
Question – Why can’t a table have more than one clustered indexes?
Answer – Clustered indexes sort and store the data rows in the table or view based on their key values so you can’t store data in table based on two index key that’s why you can create only one cluster index on a table.
Question -What is Heap and when it is useful from clustered table?
Answer – Heap is a table without having cluster indexes. Heaps are generally useful if you are running huge bulk data load or frequent DML operations.
Question – How do you find about missing indexes that you need to create to improve database performance?
Answer – We can run SQL Server database Tuning Advisor that will suggests us about all the missing indexes that we should create to improve db performance. We can also use DMVs related to missing indexes sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_columns to get these details.
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018