Free Microsoft 070-464 Exam Dumps Questions & Answers

You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows:

Product is an important table that has sensitive audit requirements.
You need to create a trigger that supports the following requirements:
1. Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate and LastUpdatedBy values in the Product table.
2. Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table.
3. Any error that occurs during the course of the trigger's execution must prevent the changes from happening.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

Correct Answer:

Explanation:
Note:
* Executing a ROLLBACK TRANSACTION or COMMIT TRANSACTION Transact-SQL statement inside a stored procedure or trigger is possible, but doing so may cause errors.

You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy.
What should you do?

• A.  Query system DMVs to monitor avg_chain_length and max_chain_length. Create alerts to notify you when these values converge.
• B.  Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value increases.
• C.  Create a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing.
• D.  Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value decreases.

You plan to design an application that temporarily stores data in a SQL Azure database.
You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session.
Which type of objects should you identify?

• A.  Common table expressions (CTEs)
• B.  Temporary stored procedures
• C.  Temporary tables
• D.  Table variables

You have the following stored procedure.

The stored procedure takes much longer to execute than expected.
While reviewing the execution plan of the stored procedure, you discover the following predicate for a Clustered Index Scan operator.

You need to resolve the performance issue.
What should you do?

• A.  Add a FORCESEEK query hint to the SELECT statement.
• B.  Convert the table to a memory-optimized table.
• C.  Change the FileName column from varchar(512) to nvarchar(512).
• D.  Change the @FileName parameter from nvarchar(512) to varchar(512).
• E.  Add a NOLOCK query hint to the SELECT statement.

You plan to create a new table that has the following requirements:
* Uses a GUID data type as the primary key.
* Uses a clustered index as the primary key.
* Minimizes fragmentation.
You need to recommend which option to include in the CREATE statement.
Which option should you include?
More than one answer choice may achieve the goal. Select the BEST answer.

• A.  IDENTITY
• B.  NEWSEQUENTIALID
• C.  NEWID
• D.  @@IDENTITY