DRAG DROP
You administer a Microsoft SQL Server database. You want to import data from a text file to the
database.
You need to ensure that the following requirements are met:
 Data import is performed by using a stored procedure.
 Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate
data import command or recovery model to the appropriate location or locations in the answer area.
Each data import command or recovery model may be used once, more than once, or not at all. You
may need to drag the split bar between panes or scroll to view content.)

You are designing a data warehouse with two fact tables. The first table contains sales per month and
the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables.What should you do?

CORRECT TEXT
You administer a Microsoft SQL Server 2012 database. The database contains a table that has the
following definition:

You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
 The first row of the file contains the first row of data.
 Each record is of the same length.
 The date follows the U.S. date format.
 The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would
configure.)

DRAG DROP
You administer a Microsoft SQL Server database. Service accounts for SQL Agents are configured to use
a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL
Server Agent job. The SSIS package accesses a network share when exporting data from a SQL Server
database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote
server. You need to ensure that the SQL Server Agent job can execute the SSIS package. Which four
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.)

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.

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 create a unique constraint?

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

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