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
Why B and not A?
Maybe because the question is “Objects created must use a minimum amount of resources”?
0
0
I think B is correct and not A because sprocs don’t use indexes.
0
0
Sprocs can use indexes.
0
0
I think the answer should be A. From Microsoft:
“When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.”
https://msdn.microsoft.com/en-us/library/cc280372(v=sql.110).aspx
Also, stored procedures absolutely can use indexes.
0
0
But if the comment from “J” is correct the answer should be “D”
0
0
First of all, stored procedures DO can use indexes.
Second, I’d go with A. That’s why:
B: Microsoft itself declares at trainings, documentations and everything else that SQL Server will create statistics for every query automatically;
C: Clustered index, on a row that contains mostly NULL values and is not unique makes no sense at all;
D: WHERE clause on the query does not refer to CustomerID, so…….
And finally, MSDN documentation os filtered indexes says all that I want to hear:
“When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.”
https://msdn.microsoft.com/en-us/library/cc280372.aspx
0
0
B
“… a good candidate for filtered statistics but not necessarily a good candidate for a filtered index if the number of … lookups is relatively small” (10% in the question) https://msdn.microsoft.com/en-us/library/ms190397(v=sql.110).aspx
0
0
From https://msdn.microsoft.com/en-us/library/ms190397(v=sql.120).aspx:
If the index is a filtered index, the query optimizer creates filtered statistics on the same subset of rows specified for the filtered index.
0
0
The query makes a distinct aggregation that could be spilling to tempdb due to inaccurate cardinality estimates. This could be the reason for the poor SP execution performance so creating the filtered statistics MAY solve the issue.
Still, a filtered index would be the best option here because resources used would still be kept to a minimum, since its just 10% of the rows that contain values.
I’m gonna go with A.
0
0