SQL Index Interview Questions

You want to improve the performance of queries that search for customers by their last name in a customers table. Which SQL statement correctly creates an index on the last_name column?

What is the primary purpose of an index in SQL?

What is the difference between clustered and non-clustered indexes?

Your queries often filter results based on a combination of country and city from the users table. Which query correctly creates a single index to optimize these types of searches?

What is a composite index and when should it be used?

How are indexes maintained when table data changes?

You need to ensure that the combination of product_id and supplier_id in a product_suppliers table is always unique, while also improving query performance on these columns. Which statement achieves both goals?

What is the difference between index scan and index seek?

When should indexes be dropped or rebuilt?

You have an index on the last_name column of the customers table. Why might the following query not use that index?
SELECT * FROM customers WHERE UPPER(last_name) = 'SMITH';