Ensurepass

QUESTION 81

You need to provide a developer the ability to create and modify database diagrams from an existing database by using SQL Server Management Studio. The solution must minimize the amount of permissions assigned to the developer. What should you do?

 

A.

Add the developer to the sysadmin role.

B.

Add the developer to the db_owner role.

C.

Grant the developer the CREATE TABLE permission only.

D.

Grant the developer the CREATE SCHEMA permission only.

 

Correct Answer: B

 

 

QUESTION 82

You are designing a database for a reporting solution that is based on data from an Online Transaction Processing (OLTP) database. The reports will contain aggregated data. You need to ensure that the reports will not affect query performance on the OLTP database. The solution must minimize the use of joins when performing the aggregate calculations. What should you do?

 

A.

Add a persisted computed column.

B.

Create indexed views in the OLTP database.

C.

Create partitioned tables in the OLTP database.

D.

Create a new denormalized database based on the OLTP database.

 

Correct Answer: D

 

 

QUESTION 83

You have a legacy application. You do not have access to the application source code. The application has a large denormalized table that contains 100 columns. The application uses stored procedures and views to perform all data manipulation language (DML) activities on the table. You need to optimize the performance of the application to meet the following requirement:

 

– Reduce I/O

– Minimize the storage requirements

– Optimize insert, update, and delete operations

 

What should you do?

 

A.

Create nonclustered indexes on all columns in the table.

B.

Create new stored procedures that use the existing views.

C.

Create new views. Perform DML activities against the views.

D.

Create smaller tables. Update the views and stored procedures.

 

Correct Answer: D

 

 

QUESTION 84

You have an instance of SQL Server 2008 that has xp_cmdshell enabled. You need to design a stored procedure that meets the following requirements:

 

– Allows authorized users to retrieve lists of files

– Minimizes permissions assigned to objects

– Minimizes security risks

 

What should you include in the design?

 

A.

Grant users permission to execute xp_cmdshell.

B.

Grant users permission to execute sp_configure.

C.

Create a procedure that uses EXECUTE AS OWNER. Call xp_cmdshell in the procedure.

Grant users permission to execute the procedure.

D.

Create a procedure that uses EXECUTE AS CALLER. Call xp_cmdshell in the procedure.

Grant users permission to execute the procedure.

 

Correct Answer: C

 

 

QUESTION 85

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a view that has the following features:

 

– It contains a WHERE clause that filters specific records.

– It allows data updates.

– You need to prevent data modifications that do not conform to the WHERE clause. You want to achieve this goal by using minimum effort.

 

What should you do?

 

A.

Create an INSTEAD OF trigger on the view.

B.

Create a unique clustered index on the view.

C.

Alter the view by adding the WITH CHECK OPTION clause.

D.

Alter the view by adding the WITH SCHEMABINDING clause.

 

Correct Answer: C

 

 

QUESTION 86

You are a SQL Server 2008 developer. You create an online transaction processing (OLTP) database by using SQL Server 2008 in an enterprise environment. The database contains a table named SalesDetails. Each record in the table contains data in any one of the following pairs of nullable columns:

 

– InternetSalesTargets and InternetSales

– ResellerSalesTargets and ResellerSales

– ForeignSalesTargets and ForeignSales

 

The table also contains three NOT NULL key columns. A large number of records are inserted on a daily basis into the SalesDetails table. Summary reports are generated from the SalesDetails table. Each report is based on aggregated data from any one of the pairs of nullable columns. You need to design a view or views to meet the following requirements:

 

– The SalesDetails table cannot be directly modified.

– The performance of the reports is maximized.

– The amount of storage space for each report is minimized.

 

What should you do?

 

A.

Create an indexed view from the SalesDetails table that contains aggregated data of all the

columns required by all the reports.

B.

Create multiple indexed views from the SalesDetails table so that each view contains aggregated

data of only the columns required by the respective report.

C.

Create multiple Report tables from the SalesDetails table so that each Report table contains

aggregated data of only the columns required by the respective report. Create views on top of each

of the Report tables.

D.

Perform a quick transfer of aggregated new records to a staging table at the end of each month.

Create an indexed view from the staging table that contains aggregated data of all the columns

required by all the reports.

 

Correct Answer: B

 

 

QUESTION 87

You are a database developer. You plan to design a database solution by using SQL Server 2008. You have a database that contains a table and a table-valued function. The tabl
e-valued function accepts the primary key from the table as a parameter. You plan to write a query that joins the table to the results of the table-valued function. You need to ensure that only rows from the table that produce a result set from the table-valued function are returned. Which join predicate should you use?

 

A.

CROSS APPLY

B.

OUTER APPLY

C.

INNER JOIN

D.

LEFT OUTER JOIN

 

Correct Answer: A

 

 

QUESTION 88

You have a table that has 10 million rows. The table has a view that returns all of the rows. You discover performance degradation when you run an unfiltered view. You need to recommend a solution to replace the view. The solution must require that the returned data is filtered by a parameter. What should you use?

 

A.

an indexed view

B.

a scalar function

C.

a table-valued function

D.

a table-valued type

 

Correct Answer: C

 

 

QUESTION 89

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a common language runtime (CLR) user-defined scalar function. The function will return an integer value. You need to ensure that the computed columns that use the result from this function can be indexed. What should you do?

 

A.

1. Ensure that the logic of the function returns the same value for the same input values and the

same database state.

2. Ensure that the IsDeterministic property is set to True.

B.

1. Ensure that the logic of the function returns a different value for the same input values and the

same database state.

2. Ensure that the IsDeterministic property is set to True.

C.

1. Ensure that the logic of the function returns the same value for the same input values and the

same database state.

2. Ensure that the IsDeterministic property is set to False.

D.

1. Ensure that the logic of the function returns a different value for the same input values and the

same database state.

2. Ensure that the IsDeterministic property is set to False.

 

Correct Answer: A

 

 

QUESTION 90

You are a database developer. You plan to design a database solution by using SQL Server 2008. You configure a database on a server to use a common language runtime (CLR). You need to create a CLR assembly that enables the CLR stored procedure to access environment variables available on the server. You also need to ensure that the CLR assembly has the minimum permissions assigned. What should you do?

 

A.

Enable the TRUSTWORTHY database property.

B.

Create the assembly by using the SAFE permission set.

C.

Create the assembly by using the UNSAFE permission set.

D.

Create the assembly by using the EXTERNAL ACCESS permission set.

 

Correct Answer: D

 

Instant Access to Download Latest Complete Collection of Microsoft MCITP 70-451 Real Exam

Try Microsoft MCITP 70-451 Free Demo

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.