More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
You want to create a covering index for this query on your SQL Server 2005 database ...
You want to create a covering index for this query on your SQL Server 2005 database:
select product, cost, productdescription
from products
where productid = @productid
The columns are as follows:
* productid - int
* product - nvarchar(80)
* cost - money
* productdescription - nvarchar(500)
What is the best solution?
Answer: CREATE INDEX IX_Cover ON dbo.Products (productid, product, cost) INCLUDE (productdescription);
Explanation: An index key can only have 900 bytes in total for all columns, so creating an index on all the columns would not work. Instead you can "include" additional columns in SQL Server 2005 that are not counted towards the 900 byte limit.
In this case, moving the productdescription to the INCLUDE clause would result in a valid index that covers this query.
(Continued on next question...)
