Download New Updated (June) Microsoft 70-469 Practice Test Topic 13, Mix Questions Set 2 part04

Ensurepass

QUESTION 205  (Topic 13)

 

You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance.

 

The application generates all T-SQL statements dynamically and does not use stored procedures.

 

You need to maximize the amount of memory available for data caching.

 

Which advanced server option should you modify?

 

A.

Optimize for Ad hoc Workloads

B.

Enable Contained Databases

C.

Allow Triggers to Fire Others

D.

Scan for Startup Procs

 

Answer: A

 

 

QUESTION 206  (Topic 13)

 

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?

 

clip_image002

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Answer: A

 

 

QUESTION 207

DRAG DROP  (Topic 13)

 

You have a SQL Azure database named Database1. You need to design the schema for a table named table1.

 

Table1 will have less than one million rows. Table1 will contain the following information for each row:

 

clip_image004

 

The solution must minimize the amount of space used to store each row. Which data types should you recommend for each column? To answer, drag the appropriate data type to the correct column in the answer area.

 

 

 

clip_image006

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 208  (Topic 13)

 

Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains

 

a database name DB1 and a table named Customers.

 

You add an additional server named SQL2 that runs SQL Server 2012.

 

You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.

 

What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

 

A.

Add SQL2 as a Distributor.

B.

Add the Customers table to SQL2.

C.

Add SQL2 as a linked server.

D.

Create the view on SQL1.

E.

Remove the Customers table from SQL1.

F.

Create the view on SQL2.

 

Answer: BCDF

 

 

QUESTION 209  (Topic 13)

 

Your network contains a server that has SQL Server 2014 installed.

 

You create a table by using the following script:

 

 

 

clip_image008

 

You need to recommend a solution to ensure that each combination of ProductName and ProductManufacturer is not duplicated.

 

What should you recommend creating?

 

A.

A UNIQUE constraint

B.

A filtered index

C.

A columnstore index

D.

A CHECK constraint

 

Answer: A

 

 

QUESTION 210  (Topic 13)

 

You are creating a stored procedure named usp1. Usp1 will create a table that will be used during the execution of usp1. Only usp1 will be allowed to access the table.

 

You need to write the code required to create the table for usp1. The solution must minimize the need to recompile the stored procedure.

 

Which code segment should you use to create the table?

 

A.

CREATE TABLE oneTable

B.< /span>

CREATE TABLE ##oneTable

C.

CREATE TABLE #oneTable

D.

DECLARE oneTable TABLE

 

Answer: B

 

 

QUESTION 211  (Topic 13)

 

You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a slow local disk.

 

You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores.

 

The stored procedure will use temporary storage defined as follows:

 

clip_image010

 

The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data.

 

For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure.

 

What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

 

 

 

clip_image012

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Answer: A

Explanation: * You must specify a value for the BUCKET_COUNT parameter when you create the memory-optimized table. In most cases the bucket count should be between 1 and 2 times the number of distinct values in the index key. If the index key contains a lot of duplicate values, on average there are more than 10 rows for each index key value, use a nonclustered index instead

You may not always be able to predict how many values a particular index key may have or will have. Performance should be acceptable if the BUCKET_COUNT value is within 5 times of the actual number of key values.

 

 

QUESTION 212  (Topic 13)

 

You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2.

 

Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table.

 

clip_image014

 

Schema1.Udf1 was defined by using the following code:

 

clip_image016

 

You need to write a query that will contain the following columns:

 

Country

CountryID

CustomerName

 

The solution must meet the following
requirements:

 

Rows must be returned only if the function returns data. The amount of time it takes the query to execute must be minimized.

 

Which query should you use?

 

 

 

clip_image018

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Answer: B

 

 

QUESTION 213  (Topic 13)

 

You have a Microsoft SQL Azure database that contains a table named Employees.

 

 

 

clip_image020

 

You create a non-clustered index named EmployeeName on the name column.

 

clip_image022

 

You write the following query to retrieve all of the employees that have a name that starts with the letters JOH:

 

You discover that the query performs a table scan.

 

You need to ensure that the query uses EmployeeName.

 

What should you do?

 

A.

Recreate EmployeeName as a unique index

B.

Recreate EmployeeName as a clustered index

C.

Replace LEFT(name,3) = ‘JOH’ by using name like ‘JOH%’

D.

Replace LEFT(name,3) = ‘JOH’ by using substring(name, 1, 3) = ‘JOH’

 

Answer: C

 

 

QUESTION 214  (Topic 13)

 

You need to encapsulate a T-SQL script into a reusable user-defined object.

 

The object must meet the following requirements:

 

ermit insertions into a table variable.

 

upport structured exception handling.

 

revent changes to the definition of referenced objects.

 

 

upport the use of the APPLY operator on the output of the object.

 

Which type of object should you use?

 

A.

An inline table-valued function

B.

A stored procedure

C.

A scalar user-defined function

D.

A multi-statement table-valued function

 

Answer: C

 

Free VCE & PDF File for Microsoft 70-469 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…