QUESTION 131

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.

 

clip_image002

clip_image004

 

 

 

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

 

A.     DateHired

B.      DepartmentID

C.      EmployeelD

D.     EmployeeNum

E.      FirstName

F.      JobTitle

G.     LastName

H.     MiddleName

I.       ReportsToID

 

Correct Answer: D

 

 

QUESTION 132

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.

 

clip_image006

clip_image008

 

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?

 

A.     DateHired

B.      DepartmentID

C.      EmployeeID

D.     EmployeeNum

E.      FirstName

F.      JobTitle

G.     LastName

H.     MiddleName

I.       ReportsToID

 

Correct Answer: C

 

 

 

 

 

 

 

 

 

 

QUESTION 133

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?

 

A.       CREATE TABLE Customer

(SourceID int NOT NULL IDENTITY,

CustomerID int NOT NULL IDENTITY,

CustomerName varchar(255) NOT NULL);

 

B.       CREATE TABLE Customer

(SourceID int NOT NULL,

CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

CustomerName varchar(255) NOT NULL);

 

C.       CREATE TABLE Customer

(SourceID int NOT NULL PRIMARY KEY CLUSTERED,

CustomerID int NOT NULL UNIQUE,

CustomerName varchar(255) NOT NULL);

 

D.       CREATE TABLE Customer

(SourceID int NOT NULL,

CustomerID int NOT NULL,

CustomerName varchar(255) NOT NULL,

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED

(SourceID, CustomerID));

 

Correct Answer: D

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 134

DRAG DROP

You are the administrator for a Data Quality Server. You are adding a user who must have

permission to:

 

Ÿ   Edit and execute a project

Ÿ   View the activity monitoring data

 

This user must not be able to:

 

Ÿ   Perform any kind of knowledge management

Ÿ   Create or change a knowledge base

Ÿ   Terminate an activity or perform administrative duties

 

You need to develop a Transact-SQL (T-SQL) script to meet these requirements. What should you

do?

 

To answer, drag the appropriate code segment or segments to the correct location or locations in

the answer area.

 

Select and Place:

clip_image009

 

Correct Answer:

clip_image010

 

 

 

QUESTION 135

DRAG DROP

A new dedicated server is used to execute resource-intensive SQL Server Integration Services

(SSIS) 2012 packages. The environment that you are deploying the packages to has the following

constraints:

 

Ÿ   < /font>The operating system is Windows Server 2008 R2.

Ÿ   The SSIS packages are stored in the SSIS catalog.

Ÿ   Some of these SSIS packages use 32-bit custom components.

 

You need to install only the components that are required to deploy and run the packages on the

new server. 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.

 

Select and Place:

clip_image012

 

Correct Answer:

clip_image014

 

 

 

 

 

QUESTION 136

HOTSPOT

You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer

dimension table by using a data flow task. Changes to the customer attributes must be tracked

over time. You need to produce a checksum value to identify the rows that have changed since

the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of

development effort to achieve this goal. Which transformation should you use?

 

To answer, select the appropriate transformation in the answer area.

 

Hot Area:

clip_image016

 

Correct Answer:

clip_image018

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 137

HOTSPOT

A SQL Server Integration Services (SSIS) package is designed to download data from a financial

database hosted in SQL Azure. The connection string to the financial database is defined as a

project parameter named FinConStr. The parameter value must be stored securely and must be

set explicitly every time the package is executed. You need to configure the parameter to meet

the requirements. What should you do?

 

To answer, configure the appropriate option or options in the dialog box in the answer area.

 

Hot Area:

clip_image020

 

Correct Answer:

clip_image022

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 138

HOTSPOT

You are the Master Data Services (MDS) administrator at your company. An existing user must be

denied access to a certain hierarchy node for an existing model. You need to configure the user’s

permissions. Which user management menu item should you select?

 

To answer, configure the appropriate option or options in the dialog box in the answer area.

 

Hot Area:

clip_image024

 

Correct Answer:

clip_image026

 

 

QUESTION 139

DRAG DROP

You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist

and Song columns. The values in the Artist column in the dataset must exactly match the values

in the Artist domain in the knowledge base. The values in the Song column in the dataset can be

a close match with the values in the Song domain. You need to use SQL Server Data Quality

Services (DQS) to define a matching policy rule to identify duplicates. How should you configure

the Rule Editor?

 

To answer, drag the appropriate answers to the answer area.

 

Select and Place:

clip_image027

 

Correct Answer:

clip_image028

 

 

QUESTION 140

HOTSPOT

You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL task in the

package checks product stock levels and sets a package variable named InStock to TRUE or FALSE

depending on the stock level found. After the successful execution of the Execute SQL task, one

of two data flow tasks must run, depending on the value of the InStock variable. You need to set

the precedence constraints. Which value for the evaluation operation should you use?

 

To answer, select the appropriate option for the evaluation operation in the answer area.

 

Hot Area:

clip_image030

clip_image032

 

Correct Answer:

clip_image034

 

Free VCE & PDF File for Microsoft 70-463 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…