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



Leave a Reply 8

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


Milen

Milen

I bet on “D”

Greg

Greg

A sounds very reasonable.

Gourd

Gourd

I think the given answer of B is correct.

https://msdn.microsoft.com/en-us/library/ms190397(v=sql.110).aspx

See: “Query Selects from a Subset of Data”

“When the query optimizer creates statistics for single columns and indexes, it creates the statistics for the values in all rows. When queries select from a subset of rows, and that subset of rows has a unique data distribution, filtered statistics can improve query plans. You can create filtered statistics by using the CREATE STATISTICS statement with the WHERE clause to define the filter predicate expression.”

Then goes on to demo a situation in the AdventureWorks2012 database.

More Material

More Material

At this moment I am going to do my breakfast, afterward having my breakfast coming again to read other news.|

ta geule

ta geule

ferme ta geule