5 03 2015

New Published Exam Microsoft 70-464 Practice Test Questions Braindump2go Guarantee You 100% Pass! (176-190)

Real Latest 70-464 Exam Questions Updated By Official Microsoft Exam Center! Braindump2go Offers 70-464 Dumps sample questions for free download now! You also can visit our website, download our premium Microsoft 70-464 Exam Real Answers, 100% Exam Pass Guaranteed!
Vendor: Microsoft
Exam Code: 70-464
Exam Name: Developing Microsoft SQL Server 2014 Databases Exam

QUESTION 176
Drag and Drop Question
You have two existing tables, one named COUNTRY and the other named STATES.
The tables are defined as follows:

You need to set up a rule that every STATE.Country_Abbr must match an existing record in the COUNTRY table.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

Answer:

QUESTION 177
You use SQL Server 2014 to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second statement.
3. Error information should be returned to the client.
What should you do?

A.    Option A
B.    Option B
C.    Option C
D.    Option D

Answer: A
Explanation:
– SET XACT_ABORT
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

QUESTION 178
Drag and Drop Question
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.

Answer:

QUESTION 179
Drag and Drop Question
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS).
During the time period when the second process executes, the disk IO can reach 7000IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day’s activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
– Both processes must never use more than 50 percent of the CPU at any one time.
– The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.
– The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
– Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

Answer:

QUESTION 180
You have the following query on a disk-based table:

You discover that the query takes a long time to complete.
The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)

The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)

You need to reduce the amount of time it takes to complete the query.
You must achieve this goal as quickly as possible.
What should you do?

A.    Reorganize the index.
B.    Update statistics.
C.    Create an index on LastName.
D.    Rebuild the index.

Answer: C

QUESTION 181
You administer an instance of SQL Server 2014.
You are tasked with tuning a common set of queries.
You have the results of several test executions, along with query plans.
The schema and the data for all database object(s) used remain unchanged between executions. The QueryTime column is defined as a computed column that uses the GETDATE() system function.
The query plans and results are shown below:
You need to make an initial diagnosis of the situation, based solely on this input.
Which two statements can you make about the performance characteristics of this query? (Each correct answer presents a complete solution. Choose two.)

A.    The queries would perform better if the index named AccountNumber included the Name and
Query Time column.
B.    The queries would perform worse if the index named AccountNumber included the
NameColumn.
C.    The queries would perform better if the index named AccountNumber included the
Name column.
D.    The object Account is a table, with an index having a leading column of AccountNumber and
a Clustered Index named PKAccount.
E.    The object Account is an indexed view, with an index having a leading column of
AccountNumber and a Clustered Index named PKAccount.
F.    The object Account is a view, joining the Account-AccountNumber and Account.PKAccount
objects together.

Answer: BD

QUESTION 182
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.)

A.    Option A
B.    Option B
C.    Option C
D.    Option D
E.    Option E

Answer: ABE
Explanation:
E: First change the NULLs to ‘ ‘.
A: Then set the default to the column to ‘ ‘.
B: Finally add the NOT NULL constraint to the column.

QUESTION 183
You have a database that uses the following management views:
– Sys.dm_os_volume_stats
– Sys.dm_db_partition_stats
– Sys.dm_db_file_space_usage
– Sys.fulltext_indexes
You plan to migrate the database to Microsoft SQL Azure.
You need to identify which view can be used in SQL Azure.
Which view should you identify?

A.    sys.dm_db_file_space_usage
B.    sys.dm_os_volume_stats
C.    sys.fulltext_indexes
D.    sys.dm_db_partition_stats

Answer: D

QUESTION 184
Drag and Drop Question
You have a SQL Server 2014 database.
You plan to create a stored procedure that will retrieve the following information:
– The XML content of the query plans that is stored in memory
– The number of times each query plan is used
You need to identify which dynamic management objects must be used to retrieve the required information for the stored procedure.
Which dynamic management objects should you identify? To answer, drag the appropriate dynamic management object to the correct requirement in the answer area.

Answer:

QUESTION 185
Drag and Drop Question
You administer a SQL Server 2014 instance.
You have been assigned to determine the cause of frequent long-running transactions that have been tracked to the dbo.Account table, where there are many cases of blocking and deadlocks. The dbo.Account table contains more than one million rows.
Users and processes frequently search for and update data by using the AccountId column, and less frequently the AccountNumber and GovernmentId columns, all of which contain only unique values. Users frequently get lists of AccountNumber values by searching on Last Name and then First Name.
You need to modify the structure of the dbo.Account table to alleviate the issues.
How should you complete the table definition to reduce contention on the table structure? To answer, drag the appropriate code snippets to the correct locations in the CREATE TABLE statement. Each code snippet may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

QUESTION 186
Drag and Drop Question
You create a disk-based table that contains the following script:

You need to prevent duplicate values in the SKU field.
Which five code segments should you use? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

Answer:

QUESTION 187
You have a SQL Server 2012 instance.
You plan to create an application that uses spatial data.
You need to create an object that will support the representation of the surface area of all the oceans.
Which code segment should you use?

A.    Option A
B.    Option B
C.    Option C
D.    Option D

Answer: B

QUESTION 188
You have a Microsoft SQL Azure database that contains a table named Customers.
You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months.
The ID of the customer is passed as an argument to the TopCustomers function.
You need to create a query that returns a list of all the Customer names and the purchase dates. The solution must return only customers that have purchased an item during the last 12 months.
What should you add to the query?

A.    CROSS JOIN
B.    OUTER APPLY
C.    OUTER JOIN
D.    CROSS APPLY

Answer: D

QUESTION 189
You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers.
Customers contains more than 1 million rows.
The database has a stored procedure that was created by using the following script:
You need to ensure that up_customers returns rows when the following statement is executed:
EXECUTEup_customers’1,2,3,4,5′;
What should you do?

A.    Update @CustcmerTypelist to use the int data type.
B.    Convert @customerTypeList to a table variable.
C.    Convert @CustomerTypeList to an XML variable.
D.    Update @CustomerTypeList to use the XML data type.

Answer: B

QUESTION 190
You use SQL Azure to store data used by an e-commerce application.
You develop a stored procedure named sp1.
Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1 is executing.
Which transaction isolation level should you use in sp1?

A.    Repeatable read
B.    Read committed
C.    Serializable
D.    Snapshot

Answer: C

QUESTION 191
Drag and Drop Question
You plan to create a custom aggregation function named Median.
You plan to deploy Median to a SQL Server 2014 server named Server1.
You need to ensure that Median can access a web service named WebApp1.
The solution must minimize the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Median.
You upload a certificate to Server1.
What three tasks should you perform next? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.