It is more faster and easier to pass the Microsoft 70-469 exam by using Precise Microsoft Recertification for MCSE: Data Platform questuins and answers. Immediate access to the Refresh 70-469 Exam and find the same core area 70-469 questions with professionally verified answers, then PASS your exam with a high score now.
Q1. You need to ensure that if any of the statements in usp_UpdateSpeakerName return an error message, all of the changes executed by usp_UpdateSpeakerName are not committed to the database.
What should you do in Procedures.sql? (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
Answer: BD
Q2. You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1. Each instance will have multiple databases.
You need to recommend a method to allocate processor time to each database.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Resource Governor
B. Max Degree of Parallelism
C. Windows System Resource Manager (WSRM)
D. Processor affinity
Answer: A
Q3. You need to convert the functionality of Legacy.sql to use a stored procedure. Which code segment should the stored procedure contain?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q4. You have a database that is accessed by 300 concurrent users.
You need to log all of the queries that become deadlocked. The solution must meet the following requirements:
. Provide a representation of the deadlock in XML format.
. Minimize the impact on the server.
What should you create?
A. A SQL Server Profiler trace
B. A script that enables trace flags
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
D. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
Answer: A
Q5. 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.
Answer: B
Q6. During performance testing, you discover that database INSERT operations against the Inventory table are slow.
You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations.
What should you recommend?
A. Partition the nonclustered index.
B. Partition the Inventory table.snapshot replication
C. Create a column store index.Master Data Services
D. Drop the clustered index.change data capture
Answer: A
Q7. You need to recommend a solution for the error handling of USP_4. The solution must handle errors for nested stored procedures in the code for USP_4.
What should you recommend?
A. Use the @@ERROR variable in the nested stored procedures.
B. Use the @@ERROR variable in USP_4.
C. Use the RAISERROR command in the nested stored procedures.
D. Use the RAISERROR command in USP_4.
Answer: C
Q8. You deploy a database by using SQL Server 2014. The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.
What should you recommend?
A. Change data capture
B. Statistics
C. A trigger
D. Master Data Services
Answer: A
Q9. You have a SQL Server 2014 instance named SQL1.
SQL1 creates error events in the Windows Application event log.
You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log.
Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)
A. A policy
B. A maintenance plan
C. An alert
D. A job
E. A trigger
Answer: DE
Q10. 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)
Answer: D
Q11. While testing the CategoryFromType function, you discover that the function is returning 'Other'.
You need to update CategoryFromType to return the category name.
Which line of code should you modify in CategoryFromType.sql?
A. 04
B. 05
C. 12
D. 14
Answer: B
Q12. Which data type should you use for ProductType?
A. varchar(11)
B. nvarchar(11)
C. char(11)
D. bigint
Answer: C
Q13. You need to recommend a solution to minimize the amount of time it takes to execute USP_5.
What should you include in the recommendation?
A. Enable cross-database chaining.
B. Use a server role to group all logins.
C. Use the EXECUTE AS clause in USP_5.
D. Copy USP.5 to each database.
Answer: A
Q14. You need to implement a solution that addresses the upload requirements. Which code segment should you use to implement the Conversions assembly?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q15. You execute IndexManagement.sql and you receive the following error message: "Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =,!
=, <, <= ,>, > = or when the subquery is used as an expression."
You need to ensure that IndexManagement.sql executes properly.
Which WHILE statement should you use at line 18?
A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)
B. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)
C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
D. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextabie)
Answer: B
Q16. You need to implement a backup strategy to support the requirements.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.
A. Create a credential called MyCredential on SQL Server by using a Windows domain account and password.
B. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO DISK...
C. Create a share on your Windows Azure site by using your Windows Azure storage account information, and grant permission to the SQL Server service login.
D. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO URL ... WTTH CREDENTIAL=N'MyCredential'
E. Create a share on the hot standby site and grant permission to the SQL Server service login.
F. Create a credential called MyCredential on SQL Server, using MyStorageAccount for the storage account name and StorageAccountKey for the access key.
G. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO SHARE ... WITH CREDENTIAL=N' MyCredential'
Answer: CD