Q1. You need to prepare the database to use the .NET Framework ProcessProducts component. 

Which code segments should you execute? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

F. Option F 

G. Option G 

View Answer

Q2. Which data type should you use for CustomerID? 

A. varchar(11) 

B. bigint 

C. nvarchar(11) 

D. char(11) 

View Answer

Q3. You are designing your maintenance plan. 

Which command should you use only during the monthly maintenance window? 

A. DBCC INDEXDEFRAG (ProdDB, SalesOrderDetail, SODIndex) 

B. ALTER INDEX SODIndex ON SalesOrderDetail REORGANIZE 

C. ALTER INDEX SODIndex ON SalesOrderDetail REBUILD 

D. ALTER INDEX SODIndex ON SalesOrderDetail REBUILD WITH (ONLINE * ON) 

View Answer

Q4. You need to create a script that automates the export of the XML data. The script must meet the integration requirements. 

What should you include in the script? 

A. The CREATE SERVER ROLE command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures. 

B. The CREATE CREDENTIAL command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures. 

C. The CREATE CREDENTIAL command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures. 

D. The CREATE SERVER ROLE command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures. 

View Answer

Q5. You need to recommend a solution that addresses the index fragmentation and index width issue. 

What should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Change the data type of the lastModified column to smalldatetime. 

B. Remove the lastModified column from the clustered index. 

C. Change the data type of the modifiedBy column to tinyint. 

D. Change the data type of the id column to bigint. 

E. Remove the modifiedBy column from the clustered index. 

F. Remove the id column from the clustered index. 

View Answer

Q6. You need to implement a solution that meets the locking requirements. 

Which line of code should you modify? 

A. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (UPDLOCK) 

B. Change line 09 in usp_GetOpenings to: FROM Openings o (ROWLOCK) 

C. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (READPAST) 

D. Change line 09 in usp_GetOpenings to: FROM Openings o (NOLOCK) 

View Answer

Q7. You need to recommend a solution to allow application users to perform tables. The solution must meet the business requirements. 

What should you recommend? 

A. Create a Policy-Based Management Policy. 

B. Create a user-defined database role and add users to the role. 

C. Create stored procedures that use EXECUTE AS clauses. 

D. Create functions that use EXECUTE AS clauses. 

View Answer

Q8. You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script: 

Each order is tracked by using one of the following statuses: 

Fulfilled 

Shipped 

Ordered 

Received 

You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future. 

What should you do? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. Implement change data capture on the Sales.Orders table. 

B. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes. 

C. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes. 

D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes. 

View Answer

Q9. You need to recommend a change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete. 

Which change should you recommend? 

A. Set the XACT_ABORT option to off 

B. Set the XACT_ABORT option to on. 

C. Set the IMPLICIT_TRANSACTIONS option to off. 

D. Set the IMPLICIT_TRANSACTIONS option to on. 

View Answer

Q10. You have a database named DB1. 

You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next. 

Occasionally, the identifying value must be reset to its initial value. 

You need to design a mechanism to hold the identifying values for the stored procedure to use. 

What should you do? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed. 

B. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE statement. Reset the value as needed by using a different ALTER SEQUENCE statement. 

C. Create a fourth table that holds the next value in the sequence. At the end each transaction, update the value by using the stored procedure. Reset the value as needed by using an UPDATE statement. 

D. Create an identity column in each of the three tables. Use the same seed and the same increment for each table. Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT command to reset the columns as needed. 

View Answer