Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 161:

  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?

  A. Create an Integration Services package and use a Data Profiling transform.
  B. Create a custom .NET application based on the Knowledgebase class.
  C. Create a data quality project.
  D. Create a CLR stored procedure based on the Knowledgebase class.
  E. Create a Master Data Services (MDS) business rule.
  C. Create a data quality project.

  ---

  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 162:

  You administer a Microsoft SQL Server database instance.

  You plan to migrate the database to Microsoft Azure SQL Database. You verify that all objects contained in the database are compatible with Microsoft Azure SQL Database.

  You need to ensure that database users and required server logins are migrated to Microsoft Azure SQL Database.

  What should you do?

  A. Use the copy database wizard
  B. Use the Database Transfer wizard
  C. Use SQL Server Management Studio to deploy the database to Microsoft Azure SQL Database
  D. Backup the database from the local server and restore it to Microsoft Azure SQL Database
  C. Use SQL Server Management Studio to deploy the database to Microsoft Azure SQL Database

• Question 163:

  HOTSPOT

  You administer two Microsoft SQL Server 2012 databases named Contoso and ContosoWarehouse.

  You plan to replicate tables from the Contoso database to the ContosoWarehouse database.

  Indexes will be added to the tables in ContosoWarehouse to improve the performance of reports.

  You need to ensure that the following requirements are met:

  Indexes are not modified when the subscriptions are reinitialized.

  Only the clustered index from Contoso will be replicated.

  Minimal transaction log activity during the reinitialization.

  What should you do? Choose the correct option(s).

  Hot Area:

  

  

• Question 164:

  You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

  

  You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

  

  

  Which Transact-SQL query should you use?

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  E. OptionE
  F. OptionF
  G. OptionG
  H. OptionH
  G. OptionG

• Question 165:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database.

  You use an OrderDetail table that has the following definition:

  

  You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column.

  Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

  ---

  Box 1: CREATE NONCLUSTERED INDEX FIndx_SpecialOfferID

  Box 2: ON dbo.OrderDetail (SalesOrderID)

  Box 3: WHERE

  WHERE Creates a filtered index by specifying which rows to include in the index.

  Box 4: (SpecialOfferID IS NOT NULL);

  References:

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

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

• Question 166:

  You administer a Microsoft SQL Server 2012 database.

  You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:

  

  You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.

  A hardware failure occurs and so a new server must be installed and configured.

  After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.

  You need to be able to restore the database.

  Which Transact-SQL statement should you use before attempting the restore?

  

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

  ---

  To create a database protected by transparent data encryption

  The following procedures show you have to create a database protected by TDE using SQL Server Management Studio and by using Transact-SQL.

  Using SQL Server Management Studio

  1.

  Create a database master key and certificate in the master database.

  2.

  Create a backup of the server certificate in the master database. Etc.

  In transact sql:

  -- Create a database master key and a certificate in the master database.

  USE master ;

  GO

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FLanddasl1';

  GO

  CREATE CERTIFICATE TestSQLServerCert

  WITH SUBJECT = 'Certificate to protect TDE key'

  GO

  -- Create a backup of the server certificate in the master database.

  -- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server -- (C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA).

  BACKUP CERTIFICATE TestSQLServerCert

  TO FILE = 'TestSQLServerCert'

  WITH PRIVATE KEY

  (

  FILE = 'SQLPrivateKeyFile',

  ENCRYPTION BY PASSWORD = '*rt@40(FLanddasl1'

  );

  GO

  Etc.

  '

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-server

• Question 167:

  DRAG DROP

  You administer several Microsoft SQL Server 2012 servers. Your company has a number of offices across the world connected by using a wide area network (WAN).

  Connections between offices vary significantly in both bandwidth and reliability.

  You need to identify the correct replication method for each scenario.

  What should you do? (To answer, drag the appropriate replication method or methods to the correct location or locations in the answer area. Each replication method may be used once, more than once, or not at all.)

  Select and Place:

  

  

  ---

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

• Question 168:

  Which of the following is the default file extension for an SQL Server 2012 backup file?

  A. .rman
  B. .bac
  C. .bak
  D. .db
  C. .bak

• Question 169:

  You administer a Microsoft Azure SQL Database instance named Inventory that contains a stored procedure named p_AddInventory.

  Users need to be able to SELECT from all tables in the database and execute the stored procedure.

  You need to grant only the necessary permissions.

  What should you do?

  A. Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.
  B. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
  C. Add all users to the db_writer role. Add all users to the db_datareader role.
  D. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Add all users to the role. Add all users to the db_datareader role.
  D. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Add all users to the role. Add all users to the db_datareader role.

  ---

  The db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database.

  References: https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/

• Question 170:

  You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders.

  Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmented.

  You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.

  Which Transact-SQL command should you use?

  A. ALTER INDEX IX_OrderDateON CustomerOrderDISABLE
  B. ALTER INDEX IX_OrderDateON CustomerOrderENABLE
  C. ALTER INDEX IX_OrderDateON CustomerOrderREORGANIZE
  D. ALTER INDEX IX OrderDateON CustomerOrderREBUILD
  C. ALTER INDEX IX_OrderDateON CustomerOrderREORGANIZE