Microsoft Developing Microsoft SQL Server Databases - 070-464 Exam Practice Test
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?
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?
Correct Answer: C
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
You need to provide referential integrity between the Sessions table and Speakers table.
Which code segment should you add at line 47 of Tables.sql?
Which code segment should you add at line 47 of Tables.sql?
Correct Answer: B
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
You need to implement a solution that addresses the bulk insert requirements.
What should you add to line 08 in usp_ImportOrderDetails?
What should you add to line 08 in usp_ImportOrderDetails?
Correct Answer: B
You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database.
Your company identifies the following requirements for each stored procedure:
USP_1 cannot allow dirty reads.
USP_2 must place range locks on the data to ensure read consistency.
You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)
Your company identifies the following requirements for each stored procedure:
USP_1 cannot allow dirty reads.
USP_2 must place range locks on the data to ensure read consistency.
You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)
Correct Answer:
Explanation:
* read committed
READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
* SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Your network contains a SQL Server 2012 instance named SQL1. SQL1 contains a database named DB1. DB1 contains three tables.
The tables are configured as shown in the following table.
You plan to create indexes for the tables.
You need to identify which type of index must be created for each table. The solution must minimize the amount of time required to return information from the tables.
Which type of index should you create for each table? To answer, drag the appropriate index type to the correct table in the answer area.
The tables are configured as shown in the following table.
You plan to create indexes for the tables.
You need to identify which type of index must be created for each table. The solution must minimize the amount of time required to return information from the tables.
Which type of index should you create for each table? To answer, drag the appropriate index type to the correct table in the answer area.
Correct Answer:
You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU).
You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
Correct Answer: A
You need to design the UserActivity table.
Which three steps should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order.
Which three steps should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
1 - Create a clustered columnstore index.
2 - Create a partitioning scheme for use by the table.
3 - Use an ALTER INDEX REORGANIZE on a specific partition.
Explanation:
Note:
* Creating a partitioned table or index typically happens in four parts:
Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column.
Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
Create or modify a table or index and specify the partition scheme as the storage location.
* Reorganizing an index uses minimal system resources.
* From scenario:
/ The index maintenance strategy for the UserActivity table must provide the optimal structure for both maintainability and query performance.
/ The CoffeeAnalytics database will combine imports of the POSTransaction and MobileLocation tables to create a UserActivity table for reports on the trends in activity. Queries against the UserActivity table will include aggregated calculations on all columns that are not used in filters or groupings.
/ When the daily maintenance finishes, micropayments that are one week old must be available for queries in UserActivity table but will be queried most frequently within their first week and will require support for in-memory queries for data within first week.
The maintenance of the UserActivity table must allow frequent maintenance on the day's most recent activities with minimal impact on the use of disk space and the resources available to queries. The processes that add data to the UserActivity table must be able to update data from any time period, even while maintenance is running.
* Columnstore indexes work well for mostly read-only queries that perform analysis on large data sets. Often, these are queries for data warehousing workloads. Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.
You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails.
For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?
For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?
Correct Answer: C
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.
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 have a table named Table1 that stores customer data.
Each customer has a credit limit that can only be discovered by querying multiple tables.
You need to ensure that the value of the credit limit is returned by executing a query on Table1.
What should you create?
Each customer has a credit limit that can only be discovered by querying multiple tables.
You need to ensure that the value of the credit limit is returned by executing a query on Table1.
What should you create?
Correct Answer: C
You need to implement a change to usp_ExportOpenings that meets the integration requirements.
What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?)
What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?)
Correct Answer: A,B,E,G
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
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?
You need to update CategoryFromType to return the category name.
Which line of code should you modify in CategoryFromType.sql?
Correct Answer: A
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.
* 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.
Correct Answer: B
The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values.
You need to update Fax to meet the following requirements:
Prevent null values from being used.
Always use an empty string instead of a null value.
Which statement or statements should you execute? (Each correct answer presents part of the solution. Choose all that apply.)
You need to update Fax to meet the following requirements:
Prevent null values from being used.
Always use an empty string instead of a null value.
Which statement or statements should you execute? (Each correct answer presents part of the solution. Choose all that apply.)
Correct Answer: A,C,D
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).