26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, sql - How do I avoid functions like UPPER in where clause, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, SQL Performance Tuning and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?
SQL Interview Question – Which index will be used?
In a recent technical interview I was asked a question about multicolumn indexes. The question says that we have table with
3 columns say column a, column b and column c. Now we create Nonclustered index on b,c & a,b. Now there a query like
select name from testIndexes WHERE b LIKE ‘Pawan’ , so which index it will use. I gave him the correct answer. Lets create a sample
table with some data and indexes.
-- CREATE TABLE testIndexes ( a INT ,b VARCHAR(10) ,c VARCHAR(10) ) GO INSERT INTO testIndexes VALUES (1,'Pawan','Nisha'),(2,'Nisha','Isha'),(3,'Isha','Pawan') CREATE NONCLUSTERED INDEX Ix_testIndexes1 ON testIndexes (b,c) CREATE NONCLUSTERED INDEX Ix_testIndexes2 ON testIndexes (a,b) SELECT b FROM testIndexes WHERE b LIKE 'Pawan' --
Now it is clear that it is using the first index, now the question is why the optimizer is using the first index. This is because the histogram builds on the first column. Now lets force the second and check out the execution plan
SELECT b FROM testIndexes WHERE b LIKE 'Pawan' GO SELECT b FROM testIndexes WITH(INDEX(Ix_testIndexes2)) WHERE b LIKE 'Pawan' GO --
In the first case it is using index seek and in the second case it is using index scan. I hope you have enjoyed the article. In the next post I will explain one more interview question asked to me in one of technical interviews I faced recently. Thanks for reading !