Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 51:

  You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.

  You need to improve the performance of the view by persisting data to disk.

  What should you do?

  A. Create an INSTEAD OF trigger on the view.
  B. Create an AFTER trigger on the view.
  C. Modify the view to use the WITH VIEW_METADATA clause.
  D. Create a clustered index on the view.
  D. Create a clustered index on the view.

  ---

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

• Question 52:

  You are creating a SQL Server Master Data Services (MDS) model for a company.

  The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.

  You need to create a hierarchy representing the organizational structure of the company.

  Which hierarchy type should you use?

  A. Natural
  B. Explicit
  C. Parent
  D. Recursive
  D. Recursive

• Question 53:

  Which of the following Microsoft SQL Server 2012 components is responsible for executing scheduled administrative tasks?

  A. SQL Server Instance
  B. SQL Server Jobs File
  C. SQL Server Agent
  D. SQL Server SSIS
  C. SQL Server Agent

  ---

  References: https://msdn.microsoft.com/en-us/library/ms189237%28v=sql.110%29.aspx

• Question 54:

  You install a Microsoft SQL Server 2012 instance.

  The instance will store data extracted from two databases running on Windows Azure SQL Database.

  You hire a data steward to perform interactive data cleansing and ad hoc querying and updating of the database.

  You need to ensure that the data steward is given the correct client tools to perform these tasks.

  Which set of tools should you install?

  A. SQL Server Management Studio and Distributed Replay Client
  B. Master Data Services and Data Quality Client
  C. Data Quality Client and Distributed Replay Client
  D. Data Quality Client and SQL Server Management Studio
  B. Master Data Services and Data Quality Client

  ---

  References: https://www.mssqltips.com/sqlservertutorial/3802/sql-server-master-data-services-architecture/

• Question 55:

  A network administrator creates a virtual machine from a template that has SQL Server installed.

  You need to ensure that you can create SQL Server databases on the new virtual machine.

  Which two actions should you perform to complete the server configuration? Each correct answer presents part of the solution.

  A. Execute the SP_ADDSERVER statement.
  B. Edit the Instance Names registry key.
  C. Execute the SP_SERVER_DIAGNOSTICS statement.
  D. Execute the SP_DROPSERVER statement.
  E. Run setup.exe and specify the upgrade option.
  A. Execute the SP_ADDSERVER statement.
  D. Execute the SP_DROPSERVER statement.

  ---

  All you should need to do is run sp_dropserver then sp_addserver to change the server name after the template is renamed. The restart the SQL instance.

  exec sp_dropserver 'OldserverName'

  go

  exec sp_addserver 'NewServerName', 'LOCAL'

  go

  References: https://serverfault.com/questions/377377/creating-a-sql-template-for-vmware

• Question 56:

  You administer a Microsoft SQL Server database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by

  UserA. GetCustomerOrderInfo selects data from both tables.

  You create a new user named UserC.

  You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC.

  Which permission or permissions should you assign to UserC? Each correct answer presents part of the solution.

  A. The Select permission on Customers
  B. The Execute permission on GetCustomerOrderInfo
  C. The Take Ownership permission on Customers
  D. The Control permission on GetCustomerOrderInfo
  E. The Take Ownership permission on Orders
  F. The Select permission on Orders
  B. The Execute permission on GetCustomerOrderInfo
  F. The Select permission on Orders

  ---

  Due to ownership chaining, you would only need to give Execute permissions to UserC to access the Customers table since UserA is the owner. You would also need to assign Select permission on the Orders table, as it is owned by UserB.

  Note: How Permissions Are Checked in a Chain When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

  References: https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

• Question 57:

  You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.

  You discover performance issues when large amounts of data are written to tables under heavy system load.

  You need to limit the number of cores that handle I/O.

  What should you configure?

  A. Processor affinity
  B. Lightweight pooling
  C. Max worker threads
  D. I/O affinity
  D. I/O affinity

  ---

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

• Question 58:

  You maintain several databases on a 64-bit Microsoft SQL 2005 instance.

  You need to migrate the databases to SQL 2012 on the same server. You also need to ensure functionality in SQL 2012 can be used in the database after migration.

  What should you do? (Each correct answer presents part of the solution.)

  A. Perform an in-place upgrade to 64-bit in SQL 2012
  B. Perform a side by side installation of a 64-bit SQL 2012
  C. Perform a side by side installation of a 32-bit SQL 2012
  D. Detach the old instance and attach it to the new one
  E. Change the compatibility level of the database
  B. Perform a side by side installation of a 64-bit SQL 2012
  D. Detach the old instance and attach it to the new one
  E. Change the compatibility level of the database

  ---

  B: In a side-by-side upgrade, SQL Server 2012 installs either along with SQL Server 2008 (or 2005) as a separate instance or on a different server. This process is essentially a new installation followed by a database migration.

  D: You can use detach and attach operations to upgrade a SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 database in SQL Server 2012. After being attached to SQL Server 2012, the database is available immediately and is

  automatically upgraded.

  E: One important factor to contemplate during an upgrade and migration effort are your database compatibility levels, which control certain database behaviors to be compatible with a particular version of SQL Server. Unlike the database file

  version (which get changed when you restore a database backup in a newer version or attach an older database to a newer version of SQL Server), the database compatibility level can be changed back and forth to different values.

  References:

  https://logicalread.com/sql-server-2012-side-by-side-upgrade-w02/#.WuiWdYhubIU

  https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms189625(v=sql.110)

  https://www.sqlskills.com/blogs/glenn/upgrading-sql-server-database-compatibility-levels/

• Question 59:

  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 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 the following requirements are met:

  The Sales role does not have the Select permission on the Customers schema.

  UserA has the Select permission on the Regions table.

  Which Transact-SQL statement should you use?

  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 FOR UserA
  I. EXEC sp_addrolemember `Sales', `UserA'
  J. EXEC sp_droprolemember `Sales', `UserA'
  D. REVOKE SELECT ON Schema::Customers FROM Sales

  ---

  References: http://msdn.microsoft.com/en-us/library/ms188369.aspx http://msdn.microsoft.com/en-us/library/ms187750.aspx http://msdn.microsoft.com/en-us/library/ff848791.aspx

• Question 60:

  DRAG DROP

  You administer a Microsoft SQL Server instance.

  An application executes a large volume of dynamic queries.

  You need to reduce the amount of memory used for cached query plans.

  Which three Transact-SQL segments should you use to develop the solution? (To answer, move the appropriate Transact-SQL segments from the list Transact-SQL segments to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

  ---

  The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in

  the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

  To do it with SP_Configure:

  -- if you haven't looked at advanced options before in SP_CONFIGURE

  SP_CONFIGURE 'Show Advanced Options', 1

  GO -- To make that online setting take effect

  RECONFIGURE

  GO -- Change Optimize for Ad Hoc Workload Setting to 1 - or enabled

  SP_CONFIGURE 'optimize for ad hoc workloads', 1

  GO RECONFIGURE

  GO

  Note: Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected. To affect already cached query plans immediately, the plan cache needs to be cleared. DBCC

  FREESYSTEMCACHE releases all unused cache entries from all caches.

  References:

  https://blogs.technet.microsoft.com/josebda/2009/03/19/sql-server-2008-optimize-for-ad-hoc-workloads/

  https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freesystemcache-transact-sql?view=sql-server-2017