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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: Clustered Index

With the same size of keys, the nonclustered indexes need more space than clustered indexes.

Box 2, Box 3:

Include the CustomerStatus column in the index, and only when CustomerStatusnot equal to 1 (the active customers).

References: http://www.sqlserverlogexplorer.com/overview-of-cluster-and-noncluster-index/

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: Clustered Index

With the same size of keys, the nonclustered indexes need more space than clustered indexes.

Box 2, Box 3:

Include the CustomerStatus column in the index, and only when CustomerStatusnot equal to 1 (the active customers).

References: http://www.sqlserverlogexplorer.com/overview-of-cluster-and-noncluster-index/

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

References: https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

References: https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

References: https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

References: https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

References: https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

References: https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

References: https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: WITH ENCRYPTION

Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

Box 2: WITH CHECK OPTION

CHECK OPTION forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

Note: Website.Customer must meet the following requirements: