Ensurepass

QUESTION 91

You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. You plan to create a stored procedure that queries a sales table and produces forecast data. You do not have administrative permissions, and you are not the owner of the database. You have permissions to create stored procedures. Users will only have permissions to execute your stored procedures. You need to ensure that users can execute the stored procedures. What should you do?

 

A.

Set the TRUSTWORTHY property of the database to ON.

B.

Include an EXECUTE AS OWNER clause when you create each stored procedure.

C.

Include an EXECUTE AS CALLER clause when you create each stored procedure.

D.

Include a SETUSER statement before you query the sales table in each stored procedure.

 

Correct Answer: B

 

 

QUESTION 92

You are a database developer. You provide solutions by using SQL Server 2008 in an enterprise environment. Your online transaction processing (OLTP) database cont
ains a tabled named SalesOrders. Your data warehouse contains a table named factBuyingHabits. The factBuyingHabits table has no indexes. You need to synchronize data between the two tables on a weekly basis. The synchronization process has the following requirements:

 

– New records in the SalesOrders table are inserted in the factBuyingHabits table.

– When a record is modified in the SalesOrders table, the modification is updated in the factBuyingHabits table.

– Records that are deleted from the SalesOrders table are also deleted from the factBuyingHabits table.

 

You need to design an appropriate synchronization solution. You want to achieve this goal by using minimum amount of coding and administrative efforts. What should you do?

 

A.

Design an SSIS package each for the INSERT, UPDATE, and DELETE operations. Schedule a job

to run this package.

B.

Design a single SSIS package that uses the Slowly Changing Dimension task. Schedule a job to run

this package.

C.

Write one stored procedure that contains a MERGE statement to perform the INSERT, UPDATE, and

DELETE operations. Schedule a job to run the stored procedure.

D.

Write three stored procedures each for the INSERT, UPDATE, and DELETE operations. Schedule a

job to run the stored procedures in a sequential manner.

 

Correct Answer: C

 

 

QUESTION 93

You need to configure a security solution for an application. The solution must meet the following requirements:

 

– The application must have access to tables in a database

– The tables must only be accessed through the application

– Database access must not require a password

 

What should you create?

 

A.

a database user that has no login

B.

a new login that has a blank password

C.

an application role

D.

a proxy object

 

Correct Answer: A

 

 

QUESTION 94

You are a database developer. You create a database by using SQL Server 2008 in an enterprise environment. The database contains two stored procedures named ModifySales and RetrieveSalesTrend. The ModifySales stored procedure uses a transaction that updates a table named SalesOrders. The RetrieveSalesTrend stored procedure retrieves and aggregates data from the SalesOrders table for a sales trend analysis report. Both stored procedures are executed frequently each day. Users report a considerable wait time while they run the sales trend analysis report. You need to ensure that sales trend analysis report runs as quickly as possible. What should you do?

 

A.

Change the isolation level to SERIALIZABLE for ModifySales.

B.

Change the isolation level to READ UNCOMITTED for ModifySales.

C.

Add the NOWAIT hint to the SELECT statement in RetrieveSalesTrend.

D.

Add the NOLOCK hint to the SELECT statements in RetrieveSalesTrend.

 

Correct Answer: D

 

 

QUESTION 95

You are designing a table to store date and time information for an application. The application will be used by users in Europe, Asia, and North America. You need to add a column that stores the date and time information for all users. The column must contain information that can be used to identify the users’ local time zone. What should you include in the design?

 

A.

a sysdatetimeoffset function

B.

a getutcdate function

C.

a datetime2 data type

D.

a datetimeoffset data type

 

Correct Answer: 
D

 

 

QUESTION 96

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a table named SaverPlans that stores information about savings plans. The table contains a column named MinBalance that stores the minimum balance for a given plan. You need to ensure that each new savings plan, for which the MinBalance value is unspecified, has a minimum balance of 1,000 U.S. dollars. You want to achieve this goal by using the minimum amount of cost.

What should you do?

 

A.

Use the DEFAULT constraint.

B.

Use an UPDATE trigger.

C.

Use the CHECK constraint.

D.

Use an INSERT trigger.

 

Correct Answer: D

 

 

QUESTION 97

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains two tables named Orders and OrderDetails. There is also a data warehouse containing a table named factSales. The factSales table has a de-normalized structure and contains columns from Orders and OrderDetails. You plan to design a solution that will extract all data modifications from Orders and OrderDetails and load them into factSales.

You have the following requirements:

 

– The load operation is incremental and runs daily.

– The schema of the tables cannot be modified.

– The history of each modification is maintained for one month.

 

You need to implement the solution by using the least amount of coding and administrative effort.

What should you do?

 

A.

Use the SQL Server Change Data Capture feature.

B.

Partition the Orders and OrderDetails tables based on date.

C.

Use the SQL Server Change Tracking feature.

D.

Use Microsoft Sync Services.

 

Correct Answer: A

 

 

QUESTION 98

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a table to stage data from an external source. The imported data will contain a field named TransactionTime. The TransactionTime field will use the hh:mm:ss.[m] format (such as 12:55:30.1).

You need to select the appropriate data type for the TransactionTime field. You want to achieve this goal by incurring minimum amount of storage cost. Which data type should you use?

 

A.

time

B.

datetime2

C.

smalldatetime

D.

time (1)

 

Correct Answer: D

 

 

QUESTION 99

You are designing a table that will contain the columns in the following table.

 

70-451-demo-16

 

You need to add a column that will display the sum of the Subtotal, TaxAmt, and FreightAmt columns. The solution must minimize the amount of storage space required to store the table.

What should you create?

 

A.

a column that uses the numeric data type

B.

a computed column

C.

a persisted computed column

D.

a column that uses the money data type

 

Correct Answer: B

 

 

QUESTION 100

You plan to deploy a new application. The application will perform the following operations:

 

– Create a new database

– Back up the new database

 

You need to configure a login to support the deployment of the new application. The solution must ensure that the application uses the most restrictive permissions possible. What should you do?

 

A.

Add the login to the sysadmin server role.

B.

Add the login to the diskadmin and secuntyadmin server roles. After the database is created,

add a user to the d
b_backupoperator database role.

C.

Add the login to the dbcreator server role.

D.

Add the login to the diskadmin and serveradmin server roles. After the database is created,

add a user to the db_backupoperator database role.

 

Correct Answer: C

 

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.