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

Explanation:
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 createadditional statistics with CREATE STATISTICS or modify the query design to improve query performance.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact-sql



Leave a Reply 1

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


ZVV

ZVV

I’d choose A.