Illustration of SQL indexing best practices, including tips for optimizing clustered and non-clustered indexes to improve query performance

6.How does the database choose which index to use when multiple indexes are available?
The database query optimizer determines which index to use based on the structure of the query. It makes a cost-based decision to use one index over another, depending on factors such as selectivity of the indexed column(s), size of the dataset and complexity of the query. You can use the EXPLAIN command to see which index is being used for a query.
Example Query:
EXPLAIN SELECT * FROM customers WHERE name = 'John Doe';
This will display which index the database uses to optimize the query.

7.What is the effect of index fragmentation, and how does it recover?
Index fragmentation occurs when the physical order of data in the index becomes out of sync with the logical order of rows. It can result in slower query performance because the database has to work hard to fetch data.
Correcting Fragmentation:
Rebuild the index: You can rebuild the index using ALTER INDEX to remove fragmentation.
Example Query:
ALTER INDEX idx_customer_name REBUILD;
Reorganize the index: For minimal fragmentation, reorganizing might be a better solution.

8.What is a covering index and when would you use it?
A covering index is an index that contains all the columns a query needs, so the database can serve the query using only the index without accessing the table data. This is very useful for read-heavy workloads.
Example Query:
CREATE INDEX idx_covering_customer ON customers (name, age, email);
If the query just selects name, age and email the database is able to use this index without having to access the customers table at all.

9.What effects do indexes have on INSERT, UPDATE and DELETE?
Every time you insert, update, or delete a row the indexes on the table need to be updated as well. That's extra work that slows down write operations, especially if there are many indexes on a table. You should minimize unnecessary indexes to get this balance right.

10.What are the differences between clustered and non-clustered indexes?
Clustered Index: The rows of the table are stored in the order defined by the index. A table has only one clustered index allowed.
Non-clustered Index: The index is stored out of the table. On a table, multiple non-clustered indexes are allowed.
Example Query (Clustered Index):
CREATE CLUSTERED INDEX idx_customer_id ON customers (customer_id);

11.How do you optimize indexes for good performance when dealing with complex queries?
For complex queries that contain multiple joins and conditions, use composite indexes that cover multiple columns involved in the query. Moreover, perform regular analysis and rebuild indexes to achieve optimal performance.
Example Query (Composite Index):
CREATE INDEX idx_name_age_email ON customers (name, age, email);
This composite index can be used to optimize the filtering query by name, age and email.

12.What is index cardinality and how does it impact the performance of an index?
Cardinality is the number of unique values in an indexed column. High cardinality is for example, ideal for indexing performance while low cardinality means there are many duplicates and in this case the index might not reduce the search space that much.

13.What are index statistics and how can they assist with query optimization?
Index statistics are statistics collected about the distribution of values within indexed columns. The database optimizer takes this information into account when choosing which index to use for which queries. Maintaining up-to-date index statistics is critical in maintaining optimal query performance.

14.When should you drop an index in SQL?
You should drop an index if it is not frequently used, if it greatly slows down the speed of writing operations or if it consumes unnecessary disk space. You can find unused indexes by tracking the execution plans of queries and performance metrics.
Example Query:
DROP INDEX idx_customer_name ON customers;

15.What is a bitmap index and when to use it?
A bitmap index is an index of special type used to represent existence in a column using bits-0s and 1s. This type of index proves to be useful when cardinality in a column is low.
for example, gender or flags with a limited number of distinct values, like 0s and 1s.
Query:
CREATE BITMAP INDEX idx_gender ON customers (gender);
This index works well for columns with few distinct values and can speed up queries that filter on those columns.