Exam 70-765 topic 3 question 56 discussion

Actual exam question from Microsoft's 70-765

Question #: 56
Topic #: 3

You administer a Microsoft SQL Server 2012 database named Orders.
Orders contains a table named OrderShip that is defined as follows:

A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.
Customers require a procedure that will return orders for all customers from a specified country.
You create a new procedure:

Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?

A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL
B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCode IS NOT NULL
C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL

Show Suggested Answer

Suggested Answer: B 🗳️
Here creating statistics is relevant. The CREATE STATISTICS command creates query optimization statistics on one or more columns of a table, an indexed view, or an external table. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact-sql

by Slava_bcd81 at Sept. 26, 2020, 7:46 a.m.

Comments

Submit Cancel

StillConfused

4 years, 5 months ago

I think the answer is C, create a clustered index because the question asks you to use minimum resources. Creating stats won't help because there are no indexes for SQL to use to actually speed up the query. Creating a clustered index uses less space than a regular index as the clustered index doesn't need the leaf level, it just reorganises the table itself.

upvoted 1 times

StillConfused

4 years, 4 months ago

Looking at the question again, if you only have *one* T-SQL statement to use, then the answer would be A because the existing PK on the table will already be clustered and you would have to alter that before you could apply a new clustered index. A not B because you still need an index to speed up the query, and this one has a where clause that means only 10% of the rows are indexed.

upvoted 3 times

...

Slava_bcd81

4 years, 7 months ago

I think correct answer is A

upvoted 2 times

...

Exam 70-765 All Questions

View all questions & answers for the 70-765 exam

Exam 70-765 topic 3 question 56 discussion

Comments

StillConfused

StillConfused

Slava_bcd81

SY0-701