top of page

Indexes to avoid table scans

The Account table was created by using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date. You frequently run the following Transact-SQL SELECT statements:

You must avoid table scans when you run the queries. You need to create one or more indexes for the table.

 

Which of the following solutions is/are correct?

Solution 1: You run the following Transact-SQL statement: CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode);

Solution 2: You run the following Transact-SQL statement: CREATE NONCLUSTERED INDEX IX_Account_ProductCode ON Account(ProductCode);

Solution 3: You run the following Transact-SQL statement:

Solution 4: You run the following Transact-SQL statement:

 

How about if I change solution 2 to:

CREATE NONCLUSTERED INDEX IDX ON dbo.<tableName> (PRODUCTCODE) INCLUDE (ACCOUNTNUMBER,BALANCE)


bottom of page