Which Transact-SQL statement should you use?

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?

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 Hint

Leave a Reply 7

Your email address will not be published. Required fields are marked *

Stan

Stan

Here the correct answer is B because: Objects created must use a minimum amount
of resources.

Reply

mr_Nobody

mr_Nobody

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

Reply

AlexanderS

AlexanderS

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.

Reply

Kevin Burgess

Kevin Burgess

Here a filtered Index works best and fulfills the requirements of the question.

A is correct.

Reply