What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008.
A database contains a table named Customer. The structure of the Customer table is as shown in the following table.

The solution uses the following stored procedure to return customer details.
CREATE PRCC GetCustomersByCountry @CountryID int
AS
SELECT customer_id, customer_name FROM Customer
WHERE country_id = @CountryID;
Users report that the stored procedure takes a long time to execute. You need to optimize the performance of the stored procedure.
What should you do?
A. Recreate the primary key as a nonclustered unique index and build a clustered index on the country_id column.
B. Build a nonclustered index on the country_id column and use the INCLUDE clause to include the customer_name column.
C. Build a nonclustered index only on the country_id column.
D. Build a nonclustered index on the country_id, customer_name, and customer_id columns.

SHOW ANSWERS

Correct Answer: B
Explanation/Reference:
In the current scenario we have only clustered index on customer_id column. As a result, execution plan of the query will be the following:

To optimize the performance, we should create a nonclustered index on the country_id column and use the INCLUDE clause to include the customer_name column. Why? Because we should design nonclustered indexes so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient. In other words, we should run the following statement:
CREATE INDEX IX_CUSTOMER_COUNTRY_ID ON dbo.customer (country_id) INCLUDE (customer_name)
GO
After that, the same query will produce the following execution plan (which is much better):

To see the difference, look at the following 2 queries and their execution plans:

You should not build a nonclustered index only on the country_id column, because in this case the index will not fully cover the query and, as a result, Key Lookup operation will be triggered.
CREATE INDEX IX_CUSTOMER_COUNTRY_ID_Only ON dbo.customer (country_id)
GO

You should not build a nonclustered index on the country_id, customer_name, and customer_id columns. customer_id will be included to the index anyway, because it is clustered index. So there is no need to specify it explicitly.
The two indexes will be the same (the same data will be in B-tree and in leaf level) in the current scenario:
CREATE INDEX IX_CUSTOMER_COUNTRY_ID_Only ON dbo.customer (country_id)
CREATE INDEX IX_CUSTOMER_COUNTRY_ID_CUSTOMER_ID ON dbo.customer (country_id, customer_id)
As for the customer_name column, there is no need to make it a key column because the query does not use it for searching. No need to put this column to B-tree, it is enough to put it only to leaf level by using INCLUDE key word.
You should not recreate the primary key as a nonclustered unique index and build a clustered index on the country_id column. This option is added for fun and should not be taken seriously 🙂

Leave a Reply Cancel reply