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
A of course….
https://msdn.microsoft.com/en-us/library/cc280372.aspx
0
0
B
0
0
Here the correct answer is B because: Objects created must use a minimum amount
of resources.
0
0
ok: A or B for sure
but… statistic wouldn’t speed up the query – and an filered index does not take that much resources (“90% are NULL”)
therfore A
“Indexes will help SQL find the data faster by creating lookups that are sorted differently than the table itself. Statistics help SQL determine how much memory/effort is going to be required to satisfy the query.”
http://dba.stackexchange.com/questions/119025/when-is-it-better-to-create-statistics-instead-of-creating-an-index
0
0
I think that B its correct. A index can work fine if you have info on the field, but the question says that “A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL”
In this case i think that statistics are best option.
0
0
Here a filtered Index works best and fulfills the requirements of the question.
A is correct.
0
0
https://msdn.microsoft.com/en-us/library/ms190397.aspx
Quote:
Filtered statistics can improve query performance for queries that select from well-defined subsets of data.
0
0