Q1. You have a SQL Server 2012 instance named SQL\Instance1. Instance1 contains a database named Database1.
You need to recommend an index defragmentation solution for an index named ContentIndex. ContentIndex must meet the following requirements:
Remain online during the defragmentation.
Update distribution statistics.
Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.
A. DBCC DBREINDEX
B. REORGANIZE
C. REBUILD
D. DBCC INDEXDEFRAG
Answer: B
Q2. You need to implement a change to usp_ExportOpenings that meets the integration requirements.
What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?)
A. To the end of line 04, add [Opening].
B. To the end of line 05, add [Opening! title].
C. To line 10, add FOR XML RAW.
D. To line 10, add FOR XMLEXPLICIT.
E. To line 10, add FOR XML AUTO.
F. To the end of line 04, add [Opening!ELEMENT].
G. To the end of line 06, add [Opening!salary!ELEMENT].
H. To the end of line 05, add [Opening!title!ELEMENT].
I. To the end of line 06, add [Opening! salary].
Answer: ABEI
Q3. You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.
What should you do?
A. Execute sp_help 'usp_GetOrdersByProduct'.
B. Execute sp_recompile 'usp_GetOrdersByProduct'.
C. Add WITH RECOMPILE to line 03 in usp_GetOrdersByProduct.
D. Add WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct.
Answer: C
Q4. You run the following code:
You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>.
What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A user-defined data type
B. An XML index
C. A Data Definition Language (DDL) trigger
D. A data manipulation language (DML) trigger
E. An XML schema collection
Answer: E
Q5. You are evaluating the table design.
You need to recommend a change to Tables.sql that reduces the amount of time it takes for usp_AttendeesReport to execute.
What should you add at line 14 of Tables.sql?
A. FullName nvarchar(100) NOT NULL CONSTRAINT DF_FuIlName DEFAULT (dbo.CreateFullName (FirstName, LastName)),
B. FullName AS (FirstName +‘ ’+ LastName),
C. FullName nvarchar(100) NOT NULL DEFAULT (dbo.CreateFullName (FirstName, LastName)).
D. FullName AS (FirstName +‘ ’+ LastName) PERSISTED,
Answer: D
Q6. You need to create the object used by the parameter of usp_InsertSessions.
Which statement should you use?
A. CREATE XML SCHEMA COLLECTION SessionDataTable
B. CREATE TYPE SessionDataTable AS Table
C. CREATE SCHEMA SessionDataTable
D. CREATE TABLE SessionDataTable
Answer: B
Q7. You have a SQL Server 2014 environment That includes four servers. The servers are configured as shown in the following table.
You plan to configure Policy-Based Management to enforce the following rules:
. On Server1, enable SQL Server password policies and enable the default trace. . On Server3, ensure that the names of user-defined stored procedures begin with the prefix "usp_" and ensure that all databases use a case-sensitive collation.
You need to recommend which server you must configure as a Central Management Server.
Which server should you recommend? (Each correct answer presents a complete solution. Choose all that apply.)
A. Server1
B. Server2
C. Server3
D. Seiver4
Answer: AC
Q8. You need to modify Production.ProductDetails_Insert to comply with the application requirements.
Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q9. You need to create a function that filters invoices by CustomerID. The SELECT statement
for the function is contained in InvoicesByCustomer.sql. Which code segment should you use to complete the function?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q10. You have a Microsoft SQL Azure database that contains a table named Customers.
You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID of the customer is passed as an argument to the TopCustomers function.
You need to create a query that returns a list of all the Customer names and the purchase dates.
The solution must return only customers that have purchased an item during the last 12 months.
What should you add to the query?
A. OUTER JOIN
B. CROSS JOIN
C. CROSS APPLY
D. OUTER APPLY
Answer: C