CREATE TABLE Sales
(
VideoID INT identity (1,1) not null Primary Key NonClustered,
VideoTitle varchar(15) NOT NULL,
RetailPrice Money NULL,
WholesalePrice Money NOT NULL,
Genre Char(15) NULL,
Barcode char(20) NOT NULL
);
You want to run the following query:
SELECT VideoID,VideoTitle, RetailPrice From Sales Order By VideoTitle
SELECT VideoTitle,Barcode,VideoID, WholesalePrice, Genre From Sales WHERE VideoID=5
What indexes should you create?
Solution:
Create Clustered index CL_videoTitle ON Sales(VideoTitle);
Create NONCLUSTERED INDEX inBV ON Sales(Barcode,VideoID)
INCLUDE (WholesalePrices, Genre);
==
You have a table named product, which has 40 columns. The most queries are as follows:
SELECT [Name],[ProductLine],[ListPrice],[Class],[Style]
FROM Product
WHERE ProductLine='T'
SELECT * FROM Product
ORDER BY [Name]
Solution:
Create a clustered index on the Name column because both queries include the [Name] column as its first field.
Create a nonclustered index on the ProductLine column and include the ListPrice,Class and Style columns.
