MS SQL Server Indexes Interview Preparation Guide
Enhance your SQL Server Indexes interview preparation with our set of 17 carefully chosen questions. These questions will test your expertise and readiness for any SQL Server Indexes interview scenario. Ideal for candidates of all levels, this collection is a must-have for your study plan. Get the free PDF download to access all 17 questions and excel in your SQL Server Indexes interview. This comprehensive guide is essential for effective study and confidence building.17 SQL Server Indexes Questions and Answers:
1 :: Explain Having clause and Where clause?
Having Clause: This clause is used for specifying a search condition for a group or an aggregate. It can only be used with a SELECT statement. It’s often used with GROUP BY clause without which its synonymous to a WHERE clause.
E.g.: SELECT Id, Name, Age FROM Customers
GROUP BY Age
HAVING Age>10
ORDER BYAge;
Where Clause: This clause is used to narrow down the dataset being dealt with following a condition.
SELECT Id, Name, Age FROM Customers
WHERE Age>10
It is strongly recommended to use a Where clause with every Select Statement to avoid a table scan and reduce the number of rows to be returned. It can be used with Select, Update, Delete etc statements.
E.g.: SELECT Id, Name, Age FROM Customers
GROUP BY Age
HAVING Age>10
ORDER BYAge;
Where Clause: This clause is used to narrow down the dataset being dealt with following a condition.
SELECT Id, Name, Age FROM Customers
WHERE Age>10
It is strongly recommended to use a Where clause with every Select Statement to avoid a table scan and reduce the number of rows to be returned. It can be used with Select, Update, Delete etc statements.
2 :: Do you know what is fill factor and pad index?
A fill factor is a specification done during the creation of indexes so that a particular amount of space can be left on a leaf level page to decrease the occurrence of page splits when the data has to be accommodated in the future.
A pad index specifies index padding. When it is set to ON, then the free space percentage per the fill factor specification is applied to the intermediate-level pages of the index. When it is set to OFF, the fill factor is not specified and enough space is left for a maximum size row that an index can have
A pad index specifies index padding. When it is set to ON, then the free space percentage per the fill factor specification is applied to the intermediate-level pages of the index. When it is set to OFF, the fill factor is not specified and enough space is left for a maximum size row that an index can have
3 :: Explain full-text indexing?
Full text indexes are stored in the file system and are administered through the database.
Only one full-text index is allowed for one table. They are grouped within the same database in full-text catalogs and are created, managed and dropped using wizards or stored procedures
Only one full-text index is allowed for one table. They are grouped within the same database in full-text catalogs and are created, managed and dropped using wizards or stored procedures
4 :: Explain what is it unwise to create wide clustered index keys?
A clustered index is a good choice for searching over a range of values. After an indexed row is found, the remaining rows being adjacent to it can be found easily. However, using wide keys with clustered indexes is not wise because these keys are also used by the non-clustered indexes for look ups and are also stored in every non-clustered index leaf entry
5 :: Explain the difference between clustered and non-clustered index?
Both stored as B-tree structure. The leaf level of a clustered index is the actual data where as leaf level of a non-clustered index is pointer to data. We can have only one clustered index in a table but we can have many non-clustered index in a table. Physical data in the table is sorted in the order of clustered index while not with the case of non-clustered data.
6 :: What is the Fillfactor concept in Indexes?
The fill factor option is provided for smoothening index data storage and performance.
The percentage of space on each leaf level page to be filled with data is determined by the fill factor value When an index is created. This reserves a percentage of free space for future growth
The percentage of space on each leaf level page to be filled with data is determined by the fill factor value When an index is created. This reserves a percentage of free space for future growth
7 :: How to maintain a fill factor in existing indexes?
Usually the data keeps getting added, deleted, or updated in the table due to which the fill factor is implemented during the index creation itself. Fill factor is not maintained after an index is created.
The changes in the future can be determined only when a new index is created on a table that has an existing data. It is beneficial to set the fill factor then itself.
Maintaining extra space on the data pages obviates the purpose of using the fill factor. The SQL Server would then have to split pages to maintain an amount of free space per the fill factor, on each page.
Thus, when data is added filling up the empty space, an index can be created and the fill factor can be re-specified distribute the data again.
The changes in the future can be determined only when a new index is created on a table that has an existing data. It is beneficial to set the fill factor then itself.
Maintaining extra space on the data pages obviates the purpose of using the fill factor. The SQL Server would then have to split pages to maintain an amount of free space per the fill factor, on each page.
Thus, when data is added filling up the empty space, an index can be created and the fill factor can be re-specified distribute the data again.
8 :: MS SQL Server index?
Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. Rows in the table are stored in the order of the clustered index key.
There can be only one clustered index per table.
Non-clustered indexes have their own storage separate from the table data storage.
Non-clustered indexes are stored as B-tree structures.
Leaf level nodes having the index key and it's row locater
Syntax:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name
There can be only one clustered index per table.
Non-clustered indexes have their own storage separate from the table data storage.
Non-clustered indexes are stored as B-tree structures.
Leaf level nodes having the index key and it's row locater
Syntax:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name
9 :: Explain Indexes disadvantages?
Use of intexes slow down Data modification operations (such as INSERT, UPDATE, DELETE).
Every time data changes in the table, all the indexes need to be updated.
Indexes need disk space, the more indexes you have, more disk space is used.
Every time data changes in the table, all the indexes need to be updated.
Indexes need disk space, the more indexes you have, more disk space is used.
10 :: Do you know Clustered and Non-Clustered Index?
Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.
Non-clustered is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.
Non-clustered is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table.