Online Access Free 070-464 Exam Questions

You need to implement a solution that addresses the index monitoring requirements.
What should you do?

• A.Schedule a SQL Server Agent job that saves data from the dynamic management views to a table in the database.
• B.Schedule a SQL Server Profiler trace, and then save the trace data to a table in the database.
• C.Create a SQL Server Audit that saves data to a log file, and then create a SQL Server Audit Specification that gathers data from the DATABASE_OPERATION group.
• D.Create a performance monitor Data Collector Set (DCS) that monitors the SQL Server counters.

You need to modify usp.GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp.GetOrdersAndItems?

• A.Add WITH (READPAST) to the end of line 47.
• B.Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44.
• C.Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44.
• D.Add WITH (NOLOCK) to the end of line 47.

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary.
You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?

• A.STATISTICS_NORECOMPUTE = OFF
• B.STATISTICS_NORECOMPUTE = ON
• C.FILLFACTOR = 0
• D.FILLFACTOR = 80

You have a Microsoft Azure SQL Database instance. The database contains a table named Table1 that has one million rows and a column named Column1. Column1 allows null values.
You need to update Column1 to meet the following requirements:
Prevent null values from being used
Always use a value of zero instead of a null value
Which three Transact-SQL statements should you run? Each correct answer presents part of the solution.

• A.ALTER TABLE dbo.Table1
  ADD CONSTRAINT
  DF_Table1_Column1
  DEFAULT 0 FOR Column1
• B.ALTER TABLE Table1
  ALTER COLUMN
  Column int NOT NULL
• C.UPDATE Table1
  SET Column1 = 0
  WHERE Column1 IS NULL
• D.ALTER TABLE Table1
  DROP COLUMN Column1
• E.ALTER TABLE Table1
  ADD COLUMN
  Column1 int NOT NULL

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

• A.Add a clustered index to the summary table.
• B.Use the summary table as an in-memory optimized table with a non-hash clustered index.
• C.Use a type variable instead of the summary table.
• D.Use the summary table as an in-memory optimized table with a non-hash nonclustered index.