DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience, the
scenario is repeated in each question. Each question presents a different goal and answer choices, but the text
of the scenario is exactly the same in each question in this series.
You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge,
and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema
named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status
and Account2Status columns are equal to one for active accounts. The following table displays selected
columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.
Website.Customer must meet the following requirements:
1. Allow users access to the CustomerName and CustomerNumber columns for active customers.
2. Allow changes to the columns that the view references. Modified data must be visible through the view.
3. Prevent the view from being published as part of Microsoft SQL Server replication.
Sales.Female.Customers must meet the following requirements:
1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.
2. Prevent changes to the columns that the view references.
3. Only allow updates through the views that adhere to the view filter.You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary.
The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL
statement:

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries.
Other stored procedures call the spDeleteCustAcctRelationship to delete records from the
CustomerToAccountBridge table.
You need to create Website Customer.
How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the
correct locations, Each Transact-SQL segment may be used once, more than once or not at all. You may need
to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience, the
scenario is repeated in each question. Each question presents a different goal and answer choices, but the text
of the scenario is exactly the same in each question in this series.
You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge,
and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema
named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status
and Account2Status columns are equal to one for active accounts. The following table displays selected
columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.
Website.Customer must meet the following requirements:
1. Allow users access to the CustomerName and CustomerNumber columns for active customers.
2. Allow changes to the columns that the view references. Modified data must be visible through the view.
3. Prevent the view from being published as part of Microsoft SQL Server replication.
Sales.Female.Customers must meet the following requirements:
1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.
2. Prevent changes to the columns that the view references.
3. Only allow updates through the views that adhere to the view filter.
You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary.
The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL
statement:

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries.
Other stored procedures call the spDeleteCustAcctRelationship to delete records from the
CustomerToAccountBridge table.
When a procedure calls spDeleteCustAcctRelationship, if the calling stored procedures has already started an
active transaction, all the detections made by the spDeleteCustAccRelationship stored procedure must be
committed by the caller; otherwise changes must be committed within the spDeleteCustAcctRelationship stored
procedure.
If any error occurs during the delete operation, only the deletes made by the soDeleteCustACCTRelationships
stored procedure must be rolled back and the status must be updated.
You need to complete the stored procedure to ensure all the requirements are met.
How should you complete the procedure? To answer, drag the Transact-SQL segments to the correct location.
Each transact-SQL segment may be used once, more than once or not at all. You may need to drag the split
bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience, the
scenario is repeated in each question. Each question presents a different goal and answer choices, but the text
of the scenario is exactly the same in each question in this series.
You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge,
and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema
named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status
and Account2Status columns are equal to one for active accounts. The following table displays selected
columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.
Website.Customer must meet the following requirements:
1. Allow users access to the CustomerName and CustomerNumber columns for active customers.
2. Allow changes to the columns that the view references. Modified data must be visible through the view.
3. Prevent the view from being published as part of Microsoft SQL Server replication.
Sales.Female.Customers must meet the following requirements:
1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.
2. Prevent changes to the columns that the view references.
3. Only allow updates through the views that adhere to the view filter.
You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary.
The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL
statement:

The following table displays a selected columns and rows from the Customer table. Thr value of the
CustomerStatus column is equal to one for active customers. The value of the Account1Status and
Account2Status columns are equal to one for active accounts.
You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries.
Other stored procedures call the spDeleteCustAcctRelationship to delete records from the
CustomerToAccountBridge table.
Users report that the following SELECT statement statement takes a long time to complete:

You need to create an index that meets the following requirements:
1. Improve the performance of the SELECT statement.
2. requires minimum index key size.
3. Only contains active customers.
4. Makes no change to the existing primary key.
5. Contains all of the columns required by the SELECT statement.
Which three Transact_SQL segments should you use to develop the solution? To answer, move the
appropriate code blocks from the list of code blocks to the answer area and arrange them in the correct order.
Select and Place:

Note: This question is part of a series of questions that use the same answer choices. An answer choice may
be correct for more than one question on the series. Each question is independent of the other questions in this
series. Information and details provided in a question apply only to that question.
You work on an OLTP database that has no memory-optimized file group defined.
You have a table names tblTransaction that is persisted on disk and contains the information described in the
following table:

Users report that the following query takes a long time to complete.

You need to create an index that:
– improves the query performance
– does not impact the existing index
– minimizes storage size of the table (inclusive of index pages).
What should you do?

Note: This question is part of a series of questions that use the same or similar answer choices. An answer
choice may be correct for more than one question in the series. Each question os independent of the other
questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1. There is no memory-optimized filegroup in the database.You run the following query:

The following image displays the execution plan the query optimizer generates for this query:

Users frequently run the same query with different values for the local variable @lastName. The table named
Person is persisted on disk.
You need to create an index on the Person.Person table that meets the following requirements:
1. All users must be able to benefit from the index.
2. FirstName must be added to the index as an included column.
What should you do?

Note: The question is part of a series of questions that use the same or similar answer choices. An answer
choice may be correct for more than one question in the series. Each question is independent of the other
question in the series. Information and details provided in a question apply only to that question.
You have a reporting database that includes a non-partitioned fact table named Fact_Sales. The table is
persisted on disk.
Users report that their queries take a long time to complete. The system administrator reports that the table
takes too much space in the database. You observe that there are no indexes defined on the table, and many
columns have repeating values.
You need to create the most efficient index on the table, minimize disk storage and improve reporting query
performance.
What should you do?

Note: The question is part of a series of questions that use the same or similar answer choices. An answer
choice may be correct for more than one question in the series. Each question is independent of the other
question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not use a memory-optimized filegroup. The database
contains a table named Table1. The table must support the following workloads:

You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing
Reporting query performance.
What should you do?

Note: The question is part of a series of questions that use the same or similar answer choices. An answer
choice may be correct for more than one question in the series. Each question is independent of the other
question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not have a memory optimized filegroup.
You create a table by running the following Transact-SQL statement:

The table is currently used for OLTP workloads. The analytics user group needs to perform real-time
operational analytics that scan most of the records in the table to aggregate on a number of columns.You need to add the most efficient index to support the analytics workload without changing the OLTP
application.
What should you do?

You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the
following issues:
– At each level of the query plan, a low total number of rows are processed.
– The query uses many operations. This results in a high overall cost for the query.
You need to identify the information that will be useful for the optimizer.
What should you do?

DRAG DROP
You are analyzing the memory usage of a Microsoft SQL Server instance.
You need to obtain the information described on the following table.

Which performance counter should you use for each requirement? To answer, drag the appropriate
performance counters to the correct requirements. Each performance counter may be used once, more than
once or not at all. You may need to drag the split bat between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place: