Online Access Free 070-464 Exam Questions

You need to modify the stored procedure usp_LookupConcurrentUsers.
What should you do?

• A.Add a clustered columnstore index to the summary table.
• B.Use a table variable instead of the summary table.
• C.Add a clustered index to the summary table.
• D.Add a nonclustered index to the summary table.

You have a SQL Azure database.
You execute the following script:

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?

• A.CREATE INDEX IX_Table1 ON Table1 (Column2)
  WHERE Column2 IS NULL
• B.CREATE INDEX IX_Table1 ON Table1 (Column2)
  WITH FILLFACTOR-0
• C.CREATE INDEX IX_Table1 ON Table1 (Column2)
  WHERE Column2 IS NOT NULL
• D.CREATE INDEX IX_Table1 OK Table1 (Column1)
  INCLUDE (Column2)

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

You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf. Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes.
A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log backups in the future.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

Correct Answer:

You review a query that runs slowly. The query accesses data in a table named Schema1.Table1.
The following is the relevant portion of the execution plan for the query:

You need to create the missing index.
Which code segment should you execute?

• A.CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3
• B.CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4)
• C.CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1)
• D.CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4)