Online Access Free 070-464 Exam Questions

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 @counter < (SELECT SUM(RowNumber) FROM @indextable)
• B.WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
• C.WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)
• D.WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)

You have a database hosted on SQL Azure.
You are developing a script to create a view that will be used to update the data in a table.
The following is the relevant portion of the script. (Line numbers are included for reference only.)

You need to ensure that the view can update the data in the table, except for the data in Column1.
Which code segment should you add at line 06?

• A.WITH SCHEMABINDING
• B.WITH VIEW_METADATA
• C.WITH ENCRYPTION
• D.WITH CHECK OPTION

You have a SQL Server 2012 database named DB1. You have a backup device named Device1.
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?

• A.BACKUP LOG DB1 TO Device1 WITH NORECOVERY
• B.BACKUP LOG DB1 TO Device1 WITH COPY_ONLY
• C.BACKUP LOG DB1 TO Device1
• D.BACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY

You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.
You need to log all of the queries to DB1 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 SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
• C.A script that enables trace flags
• D.A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName.
What should you include in the recommendation?

• A.Transactional replication
• B.Change data capture
• C.The Distributed Transaction Coordinator (DTC)
• D.Change tracking