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)


aboutME

I am John Fan Zhang, a data analyst and finance researcher. I hold a PhD in finance, CFA charter and full membership of CFA New Zealand Society. I have fifteen-year experience in corporate investment and eight-year experience in advanced data analysis. My research focuses on the effect of social psychology (culture) on financial decisions. Finance research involves heaps of data analyses that lead me to the data field. I am a Microsoft Certified Solutions Expert (MCSE): Data Management and Analytics (Excel, Power BI, and SQL). Aside from Excel, Power BI and SQL, I am also familiar with econometric tools such as Stata, Eviews, and MATLAB. I use OX and Python for programming. I am an active data community event participant, volunteer, speaker, moderator, program reviewer, including PASS Marathon 2020, Global AI BootCamp Auckland 2019, SQL Saturday Auckland (2017, 2018, 2019), and Definity Conference (2018, 2019, 2020, Auckland, New Zealand).

Auckland, New Zealand

  • Google Site
  • Twitter
  • LinkedIn

©2016 BY JOHN'S DATA STORY

bottom of page