PrepAway - Latest Free Exam Questions & Answers

Author: seenagape

You need to optimize the performance of the claim-processing queries

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database contains a table named Claims. The structure of the Claims table is as
shown in the following table. Only two percent of the claims are open at any point in time.
You discover that queries on claims that have an Open status take a long time to execute.
You need to optimize the performance of the claim-processing queries. What should you
do?

You need to recommend changes to the stored procedure to meet the following requirements…?

You have a stored procedure that uses a cursor. The stored procedure updates several
related tables. You discover that the stored procedure runs slowly and uses a significant
amount of resources on the database server. You need to recommend changes to the
stored procedure to meet the following requirements: ·Minimize execution time ·Minimize
development effort ·Minimize server resource requirements What should you recommend?

Which option should you use?

You manage a SQL Server 2008 Analysis Services (SSAS) data warehouse for a school
district.
All tests are administered via a third-party application. Results are collected in your SSAS
data warehouse and moved to a Scores fact table for analysis. This warehouse also includes
a Student Dimension which contains attributes related to each student.
The school year is starting, and the district administration wants the Student Dimension
updated hourly so that new students can be identified and tracked. However, test scores
located in the Scores fact table should not be updated as part of this incremental processing
strategy.
You need to implement this functionality.
Which option should you use?

Which Transact-SQL statement should you include at the beginning of the stored procedure?

You are writing a set of queries against a FILESTREAM-enabled database.
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a runtime error, the entire transaction is
terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

Which configuration method should you choose from the SSIS configuration options?

Your organization has five departments.
You are developing a SQL Server 2008 Integration Services (SSIS) project to calculate
salary increases and bonuses under each particular manager.
You need to develop a master package that meets the following requirements:
It must give each department a different ID.

These department IDs must pass on to each individual dependency package inside the
master package.
Each dependency package should have individual security settings to accept department ID
as a variable from its master package.
Which configuration method should you choose from the SSIS configuration options?

Where does the package execution begin?

HOTSPOT
You are working as an ETL developer for a payroll company. You configure a SQL Server
2008 Integration Services (SSIS) package to use checkpoints.
The SSIS package truncates the dbo.Employee_Attendance table, loads the new employees
in the dbo.Employee table, and then loads dbo.Employee_Attendance for a particular month.
The package fails at the Derived Column transformation within the data flow task.
You need to determine where the package execution will begin after you correct the errors
and run the package again.
Where does the package execution begin? (To answer, select the appropriate task in the
control flow.)

You need to ensure that all the tasks of the package execute within a single transaction

You are developing a SQL Server 2008 Integration Services (SSIS) package.
The package performs the following tasks:
Processes multiple files by using a ForEach Loop container.
Imports the contents of the files to a table by using a Data Flow task.
Logs the results of the status into a table by using an Execute SQL task.
You need to ensure that all the tasks of the package execute within a single transaction.
What should you do?