Exam 70-762 topic 1 question 169 discussion

Actual exam question from Microsoft's 70-762

Question #: 169
Topic #: 1

Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:

You need to create one nonclustered covering index that contains all of the columns in the above table. You must minimize index key size.
Which Transact-SQL statement should you run?

A. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);
B. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);
C. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);
D. CREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);

Show Suggested Answer

Suggested Answer: D 🗳️
Use the UserStatus as the first column in the index, as it is an in_equality column.
Incorrect Answers:
A: UserStatus is not included.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

by EdwardWang at Dec. 30, 2019, 10:11 p.m.

Comments

Submit Cancel

EdwardWang

Highly Voted 5 years, 5 months ago

B is correct one

upvoted 27 times

...

JohnFan

Highly Voted 5 years, 4 months ago

When creating a multi-column index it is important to make sure you place equality columns first in your index, prior to inequality columns. This is because the optimizer stops an index seek operation on your index after finding the first inequality column used in your TSQL statement. If you place all the equality columns first in your index, SQL Server can more efficiently perform an index seek operation and allows SQL Server to perform less I/O. https://www.databasejournal.com/features/mssql/article.php/3866881/SQL-Server-Column-Considerations-and-Column-Placement.htm

upvoted 15 times

...

Alex5x

Most Recent 4 years, 7 months ago

The right answer is B. From Microsoft (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-details-transact-sql?view=sql-server-ver15#using-missing-index-information-in-create-index-statements): To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement for both memory-optimized and disk-based indexes, equality columns should be put before the inequality columns, and together they should make the key of the index. Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list).

upvoted 2 times

...

strikersree

4 years, 11 months ago

B is the correct one

upvoted 2 times

...

lukadataowner

5 years, 1 month ago

only B is correct

upvoted 4 times

...

Nelly100

5 years, 2 months ago

The correct Answer is B. Equality columns first that the rule for query efficiency. https://www.databasejournal.com/features/mssql/article.php/3866881/SQL-Server-Column-Considerations-and-Column-Placement.htm

upvoted 5 times

...

paee45

5 years, 5 months ago

the explanation is not correct, Userstatus is Inequality column and it should be after! > B

upvoted 3 times

...