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

Ensurepass

QUESTION 215

DRAG DROP  (Topic 13)

 

You plan to create a new table that will contain a column named Salary. Salary will contain highly sensitive data.

 

Salary must meet the following requirements:

 

Contain numeric data.

Contain only encrypted data that remains encrypted in memory.

 

You need to identify which encryption type and data type must be used for Salary.

 

Which encryption type and data type should you identity?

 

To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area.

 

 

 

clip_image002

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 216  (Topic 13)

 

You are creating a table to support an application that will cache data outside of SQL Server.

 

The application will detect whether cached values were changed before it updates the values.

 

You need to create the table, and then verify that you can insert a row into the table.

 

Which code segment should you use?

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Answer: C

Explanation:http://msdn.microsoft.com/en-us/library/ms182776.aspx http://msdn.microsoft.com/en-us/library/ms187942.aspx http://msdn.microsoft.com/en-us/library/ms190348.aspx

 

 

QUESTION 217  (Topic 13)

 

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.

OUTER JOIN

B.

CROSS JOIN

C.

CROSS APPLY

D.

OUTER APPLY

 

Answer: C

 

 

QUESTION 218  (Topic 13)

 

You have a database that is accessed by 300 concurrent users.

 

You need to log all of the queries that become deadlocked. The solution must meet the following requirements:

 

 

Provide a representation of the deadlock in XML format.

Minimize the impact on the server.

 

What should you create?

 

A.

A SQL Server Profiler trace

B.

A script that enables trace flags

C.

A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views

D.

A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views

 

Answer: A

Explanation: Analyze Deadlocks with SQL Server Profiler Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio.

 

 

QUESTION 219  (Topic 13)

 

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:

 

 

 

clip_image004

 

You need to ensure that up_customers returns rows when the following statement is executed:

 

EXECUTE up_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 220  (Topic 13)

 

You have a database that contains a table named Department. Department contains the names and locations of each department.

 

You have a table-valued function named ProjectList() that returns a list of all the projects assigned to a department. The name of the department is passed as an argument to the ProjectList() function.

 

You need to create a query that returns a list of all the department names and the project names. The solution must return only departments that are associated to projects.

 

What should you add to the query?

 

A.

OUTER APPLY

B.

OUTER JOIN

C.

CROSS JOIN

D.

CROSS APPLY

 

Answer: D

 

 

QUESTION 221  (Topic 13)

 

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

 

clip_image006

 

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 222  (Topic 13)

 

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.

 

A.

A Data Definition Language (DDL) trigger

B.

A DEFAULT constraint

C.

A CHECK constraint

D.

A FOREIGN KEY constraint

E.

A data manipulation language (DML) trigger

 

Answer: E

Explanation:http://www.techrepublic.com/blog/programming-and-development/comparing-sql- serverconstraints-and-dmltriggers/402

http://msdn.microsoft.com/en-us/library/ms178110.aspx

 

 

QUESTION 223  (Topic 13)

 

 

You use SQL Server 2012 to maintain the data used by the applications at your company.

 

You plan to create a table named Table1 by using the following statement. (Line numbers are included for reference only.)

 

clip_image008

 

You need to ensure that Table1 contains a column named UserName. The UserName column will:

 

Store string values in any language.

Accept a maximum of 200 characters.

Be case-insensitive and accent-insensitive.

 

Which code segment should you add at line 03?

 

A.

UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL,

B.

UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL,

C.

UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL,

D.

UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL,

 

Answer: D

 

 

QUESTION 224

DRAG DROP  (Topic 13)

 

You plan to create a custom aggregation function named Function1.

 

You plan to deploy Function1 to SQL Server 2012.

 

You need to ensure that Function1 can access a web service. The solution must minimize the number of changes made to the database.

 

You create a Microsoft .NET Framework class that contains the definition of Function1.

 

You upload a certificate to SQL Server.

 

 

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.

 

clip_image010

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

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…