You administer a Microsoft SQL Server 2012 database. The database contains a table
named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

Confidential information about the employees is stored in a separate table named
EmployeeData. One record exists within EmployeeData for each record in the Employee
table. You need to assign the appropriate constraints and table properties to ensure data
integrity and visibility. On which column in the Employee table should you use an identity
specification to include a seed of 1,000 and an increment of 1?

You are a database developer of a Microsoft SQL Server 2012 database. You are designing
a table that will store Customer data from different sources. The table will include a column
that contains the CustomerID from the source system and a column that contains the
SourceID. A sample of this data is as shown in the following table. You need to ensure that

the table has no duplicate CustomerID within a SourceID. You also need to ensure that the
data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?

HOTSPOT
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements:
Include multiple properties in a configuration.
Support several packages with different configuration settings.
You need to select the appropriate configuration. Which configuration type should you use?
To answer, select the appropriate option from the drop-down list in the dialog box.

You are designing a SQL Server Integration Services (SSIS) solution. The solution will
contain an SSIS project that includes several SSIS packages. Each SSIS package will
define the same connection managers and variables.
You have the following requirements:
Ensure that the deployment model supports changing the content of connection strings by
using parameters at execution time.
Ensure that the deployment model automatically starts from calls to the
catalog.start_execution stored procedure in the SSISDB database.
Maximize performance at execution time.
Minimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)

DRAG DROP
You are creating a SQL Server Integration Services (SSIS) package to populate a fact table
from a source table. The fact table and source table are located in a SQL Azure database.
The source table has a price field and a tax field. The OLE DB source uses the data access
mode of Table.
You have the following requirements:
The fact table must populate a column named TotalCost that computes the sum of the price
and tax columns.
Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To
answer, move the appropriate components from the list of components to the answer area
and arrange them in the correct order.)

DRAG DROP
You are designing a SQL Server Integration Services (SSIS) package to execute 12
Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may
be executed in any order. The T-SQL statements have unpredictable execution times.
You have the following requirements:
The package must maximize parallel processing of the T-SQL statements.
After all the T-SQL statements have completed, a Send Mail task must notify administrators.
You need to design the SSIS package. Which three actions should you perform in
sequence? (To answer, move the appropriate actions from the list of actions to the answer
area and arrange them in the correct order.)

DRAG DROP
You plan to deploy a SQL Server Integration Services (SSIS) project by using the project
deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer
than usual. Which three actions should you perform in sequence? (To answer, move the
appropriate actions from the list ofactions to the answer area and arrange them in the correct order.)

You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of
millions of new records are loaded into the data warehouse weekly, outside of business
hours.
Most queries are generated by reports and by cube processing. Data is frequently queried at
the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries. What should you
do? (More than one answer choice may achieve the goal. Select the BEST answer.)

You are designing an extract, transform, load (ETL) process for loading data from a SQL
Server database into a large fact table in a data warehouse each day with the prior day’s
sales data.
The ETL process for the fact table must meet the following requirements:
Load new data in the shortest possible time.
Remove data that is more than 36 months old.
Ensure that data loads correctly.
Minimize record locking.
Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements. What should you do?
(More than one answer choice may achieve the goal. Select the BEST answer.)

DRAG DROP
You are validating whether a SQL Server Integration Services (SSIS) package named
Master.dtsx in the SSIS catalog is executing correctly.
You need to display the number of rows in each buffer passed between each data flow
component of the package.
Which three actions should you perform in sequence? (To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.)