Category «70-464»

Exam 70-464: Developing Microsoft SQL Server 2012 Databases (update January 2nd, 2018)

How should you complete the statement?

DRAG DROPYou use the following statement to create a table. You have the following queries. You need to create an index to minimize the execution time of the queries.How should you complete the statement? To answer, drag the appropriate code elements to the correctlocations. Each code element may be used once, more than once, or …

Which isolation level should you identify?

DRAG DROPYou 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 must 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. …

You need to resolve the performance issue

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 ClusteredIndex Scan operator. You need to resolve the performance issue.What should you do? A. Change the @FileName parameter from nvarchar(512) to varchar(512). B. Change …

Which storage option should you use?

You are designing a new database table that will be used for reporting.You define the table by using the following statement. You need to store the data in the table by using the least amount of storage space possible.Which storage option should you use? A. a clustered index B. a clustered columnstore index C. a …

You need to perform the following maintenance tasks:

DRAG DROPYou have a database named database1. Each table in database1 has one index per column.Users often report that creating items takes a long time.You need to perform the following maintenance tasks:Identify unused indexes.Identify which indexes should be created.What should you use?To answer, drag the appropriate function to the correct management task in the answer …

Which Transact-SQL statement should you run?

You have a database named Inventory. Inventory is stored on Microsoft Azure SQL Database and contains atable named ProductDetails.For a given product, you need to retrieve the ProductID, Manufacturer, Weight, and ListPrice columns for all ofthe items in the ProductDetails table. The solution must ensure that the results can be joined to other tables.Which Transact-SQL …

you need to deal with temporary data in the most efficient way to minimize physical disk pressure.

You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAMavailable. There is approximately 50GB of storage space available on a slow local disk. You create a newstored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, fromwhich you will compute two complex …

You need to ensure that Person.Contact is always updated when UpdateContact executes.

You have a Microsoft SQL Azure database.You have the following stored procedure: You discover that the stored procedure periodically fails to update Person.Contact.You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution mustminimize the amount of time required for the stored procedure to execute and the number of locks held.What should …

How should you complete the table definition to reduce contention on the table structure?

DRAG DROPYou administer a SQL Server 2014 instance.You have been assigned to determine the cause of frequent long-running transactions that have been trackedto the dbo.Account table, where there are many cases of blocking and deadlocks. The dbo.Account tablecontains more than one million rows.Users and processes frequently search for and update data by using the AccountId …