Microsoft 70-458 Online Practice Questions and Exam Preparation

Microsoft 70-458 Online Questions & Answers

• Question 11:

  Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

  You administer a SQL 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named userA is a member of a role named Sales.

  UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.

  You need to remove the Select permission for userA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.

  A. DENY SELECT ON Object::Regions FROM Sales
  B. DENY SELECT ON Schema:: Customers FROM Soles
  C. REVOKE SELECT ON Object::Regions FROM Soles
  D. REVOKE SELECT ON Schema: Customers FROM Soles
  E. DENY SELECT ON Object::Regions FROM UserA
  F. DENY SELECT ON Schema: Customers FROM UserA
  G. REVOKE SELECT ON Object::Regions FROM UserA
  H. REVOKE SELECT ON Schema::Customers FROM UserA
  I. EXEC sp_oddrolemember 'Sales', 'UserA'
  J. EXEC 3p_droprolemember 'Sales', 'UserA'
  G. REVOKE SELECT ON Object::Regions FROM UserA

• Question 12:

  Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

  You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named userA is a member of a role named Sales.

  UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.

  You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema.

  A. DENY SELECT ON Object::Regions FROM Sales
  B. DENY SELECT ON Schema::Customers FROM Sales
  C. REVOKE SELECT ON Object::Regions FROM Sales
  D. REVOKE SELECT ON Schema::Customers FROM Sales
  E. DENY SELECT ON Object::Regions FROM UserA
  F. DENY SELECT ON Schema::Customers FROM UserA
  G. REVOKE SELECT ON Object::Regions FROM UserA
  H. REVOKE SELECT ON Schema::Customers FROM UserA
  I. EXEC sp_addrolemember 'Sales', 'UserA'
  J. EXEC sp_droprolemember 'Sales', 'UserA'
  F. DENY SELECT ON Schema::Customers FROM UserA

• Question 13:

  A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.

  The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes.

  You need to minimize the package execution time.

  What should you do?

  A. Reduce the value of the Maximum Insert Commit Size property.
  B. Partition the table by day and store.
  C. Create a clustered index on the Transaction table.
  D. Run the package in Performance mode.
  B. Partition the table by day and store.

• Question 14:

  You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.

  Some of the data to be loaded will reference products that need special processing logic in the data flow.

  You need to enable separate processing streams for a subset of rows based on the source product code.

  A. Script Task
  B. Conditional Split
  C. Multicast
  D. Source Assistant
  B. Conditional Split

• Question 15:

  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, configure the appropriate option or options in the dialog box in the answer area.)

  

  Hot Area:

  

  

• Question 16:

  You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy.

  The package reads data from a source system. Depending on the value in a source column, the package redirects rows to one of five different data flow paths.

  You need to add a data flow transformation to support the package redirection.

  A. Lookup
  B. Multicast
  C. Conditional Split
  D. Pivot
  C. Conditional Split

• Question 17:

  You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Mode. The project will be deployed to an SSIS catalog folder where Environments have already been created.

  You need to deploy the project.

  A. Use an event handler for OnError for the package.
  B. Use an event handler for OnError for each data flow task.
  C. Use an event handler for OnTaskFailed for the package.
  D. View the job history for the SQL Server Agent job.
  E. View the All Messages subsection of the All Executions report for the package.
  F. Store the System::SourceID variable in the custom log table.
  G. Store the System::ServerExecutionID variable in the custom log table.
  H. Store the System::ExecutionInstanceGUID variable in the custom log table.
  I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
  J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
  M

• Question 18:

  You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. What should you do?

  A. Query the sys.dm_db_missing_index_details DMV.
  B. Query the sys.dm_db_index_usage_stats DMV.
  C. Query the sys.dm_db_missing_index_columns DMV.
  D. Use the Database Engine Tuning Advisor.
  D. Use the Database Engine Tuning Advisor.

• Question 19:

  You administer a Microsoft SQL Server 2012 database.

  You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01.

  You need to configure the availability group to minimize transaction latency on any available secondary databases. In the event of a database failure, the designated secondary database should come online automatically.

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  D. Option D

• Question 20:

  You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.

  The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server.

  The project must be configured to be able to export data to and from five different production servers that run SQL Server 2012. Each target server requires different values for connection strings and parameters in the SSIS project.

  You need to meet the requirements by using the least amount of administrative effort.

  What should you do?

  A. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.
  B. Create one registry configuration. Add a connection string for each target server. Choose the appropriate server in the package at build time.
  C. Create one XML configuration file. Add a connection string for each target server. Choose the appropriate server in the package at build time.
  D. For each target server, create a separate package and include each package in the SSIS catalog. Run the appropriate server at package execution time.
  C. Create one XML configuration file. Add a connection string for each target server. Choose the appropriate server in the package at build time.