Ensurepass

QUESTION 11

You have a server named Server1 that has 16 processors. You plan to deploy multiple instances of

SQL Server 2012 to Server1. You need to recommend a method to allocate processors to each

instance. What should you include in the recommendation? More than one answer choice may

achieve the goal. Select the BEST answer.

 

  1. Max Degree of Parallelism

  2. Processor affinity

  3. Windows System Resource Manager (WSRM)

  4. Resource Governor

 

Correct Answer: B

 

 

QUESTION 12

You have a SQL Azure database. You need to identify which keyword must be used to create a

view that will be indexed. Which keyword should you identify?

 

  1. DISTINCT

  2. DEFAULT

  3. SCHEMABINDING

  4. VIEW_METADATA

 

Correct Answer: C

 

 

QUESTION 13

You have a database hosted on SQL Azure. You are developing a script to create a view that will

be used to update the data in a table. The following is the relevant portion of the script.

(Line numbers are included for reference only.)

 

70-459-demo-5

 

You need to ensure that the view can update the data in the table, except for the data in

Column1. Which code segment should you add at line 06?

 

  1. WITH VIEW_METADATA

  2. WITH ENCRYPTION

  3. WITH CHECK OPTION

  4. WITH SCHEMABINDING

 

Correct Answer: C

 

 

QUESTION 14

You have a text file that contains an XML Schema Definition (XSD). You have a table named

Schema1.Table1. You have a stored procedur
e named Schema1.Proc1 that accepts an XML

parameter named Param1. You need to store validated XML data in Schema1.Table1. The solution

must ensure that only valid XML data is accepted by Param1. What should you do?

(Each correct answer presents part of the solution. Choose all that apply.)

 

  1. Use the modify method to insert the XML schema into each row of the XML column in

Table1.

  1. Define an XML column in Table1 by using an XML schema collection.

  2. Declare Param1 as type XML and associate the variable to the XML schema collection.

  3. Create an XML schema collection in the database from the text file.

 

Correct Answer: ABD

 

 

QUESTION 15

You have an index for a table in a SQL Azure database. The database is used for Online

Transaction Processing (OLTP). You discover that many page splits occur when records are

inserted or updated in the table. You need to minimize the number of page splits. What should

you set from the index options?

 

  1. FILLFACTOR = 0

  2. STATISTICS_NORECOMPUTE = ON

  3. STATISTICS_NORECOMPUTE = OFF

  4. FILLFACTOR = 80

 

Correct Answer: D

 

 

QUESTION 16

You have a SQL Azure database. You execute the following script:

 

70-459-demo-6

 

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for

Column2. You plan to deploy an application that will search Column2. You need to create an

index on Table1 to support the planned deployment. The solution must minimize the storage

requirements. Which code segment should you execute?

 

  1. CREATE INDEX IX_Table1 ON Table1 (Column1)

INCLUDE (Column2)

  1. CREATE INDEX IX_Table1 ON Table1 (Column2)

WHERE Column2 IS NOT NULL

  1. CREATE INDEX IX_Table1 ON Table1 (Column2)

WHERE Column2 IS NULL

  1. CREATE INDEX IX_Table1 ON Table1 (Column2)

WITH FILLFACTOR=0

 

Correct Answer: B

 

 

QUESTION 17

You are creating a table named Orders. You need to ensure that every time a new row is added to

the Orders table, a table that is used for auditing is updated. What should you use? More than

one answer choice may achieve the goal. Select the BEST answer.

 

  1. a DEFAULT constraint

  2. a Data Definition Language (DDL) trigger

  3. a CHECK constraint

  4. a FOREIGN KEY constraint

  5. a data manipulation language (DML) trigger

 

Correct Answer: E

 

 

QUESTION 18

You have a database named database1. Database developers report that there are many

deadlocks. You need to implement a solution to monitor the deadlocks. The solution must meet

the following requirements:

 

  • Support real-time monitoring.

  • Be enabled and disabled easily.

  • Support querying of the monitored data.

 

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

BEST answer.

 

  1. a SQL Server Profiler template

  2. an Extended Events session

  3. log errors by using trace flag 1204

  4. log errors by using trace flag 1222

 

Correct Answer: B

 

 

QUESTION 19

You have a server that has SQL Server 2012 installed. You need to identify which parallel

execution plans are running in serial. Which tool should you use?

 

  1. Performance Monitor

  2. Database Engine Tuning Advisor

  3. Extended Events

  4. Data Profile Viewer

 

Correct Answer: C

 

 

QUESTION 20

You execute the following code:

 

70-459-demo-7

 

After populating the Employees table with 10,000 rows, you execute the following query:

 

70-459-demo-8

 

You need to reduce the amount of time it takes to execute the query. What should you do?

 

  1. Change SUBSTRING(JobTitle,1, 1) = ‘C’ to JobTitle LIKE ‘C%’.

  2. Partition the table and use the JobTitle column for the partition scheme.

  3. Replace IX_Employees with a clustered index.

  4. Change SUBSTRING (JobTitle, 1, 1) = ‘C’ to LEFT(JobTitle ,1) = ‘C’.

 

Correct Answer: A

 

Instant Access to Download Latest Complete Collection of Microsoft MCSE 70-459 Real Exam

Try Microsoft MCSE 70-459 Free Demo

Comments are closed.