Microsoft 70-462 Exam Practice Test Instant Access

Question 1

You are the data steward for a Business Intelligence project.

You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file.

A Data Quality Services (DQS) knowledge base has been created to support this project.

You need to produce the CSV file with the least amount of development effort.

What should you do?

ACreate an Integration Services package and use a Data Profiling transform.

BCreate a custom .NET application based on the Knowledgebase class.

CCreate a data quality project.

DCreate a CLR stored procedure based on the Knowledgebase class.

ECreate a Master Data Services (MDS) business rule.

Answer : C

References:

http://msdn.microsoft.com/en-us/library/hh213052.aspx

http://msdn.microsoft.com/en-us/library/ff877917.aspx

http://msdn.microsoft.com/enus/library/microsoft.masterdataservices.services.datacontracts.knowledgebase.aspx

http://msdn.microsoft.com/en-us/library/bb895263.aspx

Question 2

You are implementing the indexing strategy for a fact table in a data warehouse.

The fact table is named Quotes.

The table has no indexes and consists of seven columns:

* [ID]

* [QuoteDate]

* [Open]

* [Close]

* [High]

* [Low]

* [Volume]

Each of the following queries must be able to use a columnstore index:

* SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.

* SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.

* SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.

You need to ensure that the indexing strategy meets the requirements.

The strategy must also minimize the number and size of the indexes.

What should you do?

ACreate one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].

BCreate three coiumnstore indexes:One containing [QuoteDate] and [Close]One containing [QuoteDate], [High], and [Low]One containing [QuoteDate] and [Volume]

CCreate one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].

DCreate two columnstore indexes:One containing [ID], [QuoteDate], [Volume], and [Close]One containing [ID], [QuoteDate], [High], and [Low]

Answer : C

References:

http://msdn.microsoft.com/en-us/library/gg492088.aspx

http://msdn.microsoft.com/en-us/library/gg492153.aspx

Question 3

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?

AJoin the two fact tables.

BMerge the fact tables.

CCreate a time dimension that can join to both fact tables at their respective granularity.

DCreate a surrogate key for the time dimension.

Answer : D

Question 4

You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3.

You have permissions on both Database1 and Database2.

You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.

You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.

Which Transact-SQL statement should you use?

AUSE Database2

BEXECUTE AS OWNER

CUSE Database1

DEXECUTE AS CALLER

Answer : B

References:

http://msdn.microsoft.com/en-us/library/ms188354.aspx

http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-callingstoredprocedure-on-linked-server/

Question 5

You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role namedBillingUsers.

All objects in Contoso are in the dbo schema.

You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers.

Which Transact-SQL statement should you use?

ACREATE ROLE proc_callerGRANT EXECUTE ON Schema: : dbo TO proc_callerALTER ROLE proc_caller ADD MEMBER BillingUsers

BGRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers

CEXEC sp_addrolemember 'executor', 'BillingUsers'

DCREATE ROLE proc_callerGRANT EXECUTE ON ALL PROCEDURES TO proc_callerALTER MEMBER BillingUsers ADD TO ROLE proc_caller

Answer : D

Question 6

You are the administrator of a Microsoft SQL Server 2012 server.

Some applications consume significant resources.

You need to manage the server workload by restricting resource-intensive applications. You need to dynamically limit resource consumption.

What should you do?

ASet up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources.

BConfigure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor.

CConfigure Extended Events to monitor and restrict resource limits allowed by each application type.

DCreate a new Plan Guide with a Scope Type of SQL and define the resource limits for each application.

Answer : B

SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.

The following three concepts are fundamental to understanding and using Resource Governor:

References:

https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

Question 7

You plan to migrate the database to Azure. You verify that all objects are valid for Azure SQL database. You need to ensure that users and logins are migrated to Azure.

What should you do?

AUse the Copy Database wizard

BUse the Database Transfer wizard

CUse the SQL Management Studio to deploy the database to Azure

DBack up the database from the local server and restore it to Azure

Answer : C

Use SQL Server Management Studio to establish a connection to your Azure SQL Database server.

Steps:

Create an empty Azure SQL database in the Azure portal (using a new or existing Azure SQL Database server)

Create a server-level firewall in the Azure portal (if not previously created)

Use the Data Migration Assistant (DMA) to import your SQL Server database into the empty Azure SQL database

Use SQL Server Management Studio (SSMS) to change database properties.

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database

Microsoft 70-462 Administering Microsoft SQL Server 2012 Databases Exam Practice Test