Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 291:

  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?

  A. Join the two fact tables.
  B. Merge the fact tables.
  C. Create a time dimension that can join to both fact tables at their respective granularity.
  D. Create a surrogate key for the time dimension.
  D. Create a surrogate key for the time dimension.

  ---

  With dimensionally modeled star schemas or snowflake schemas, decision support queries follow a typical pattern: the query selects several measures of interest from the fact table, joins the fact rows with one or several dimensions along the surrogate keys, places filter predicates on the business columns of the dimension tables, groups by one or several business columns, and aggregates the measures retrieved from the fact table over a period of time. The following demonstrates this pattern, which is also sometimes referred to as a star join query: select ProductAlternateKey, CalendarYear,sum(SalesAmount) from FactInternetSales Fact join DimTime on Fact.OrderDateKey = TimeKey join DimProduct on DimProduct.ProductKey = Fact.ProductKey where CalendarYear between 2003 and 2004 and ProductAlternateKey like 'BK%' group by ProductAlternateKey,CalendarYear

• Question 292:

  You install Microsoft SQL Server 2012 on a new server.

  After setup is complete, you attempt to start the SQL Server service. After being in a starting state for a few moments, the service goes back to a stopped state.

  You need to determine the cause of the failure.

  Which file should you use?

  A. %programfiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\Errorlog
  B. %programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt
  C. %programfiles%\Microsoft SQL Server\110\Shared\ErrorDumps\SQLDmpr[XXXX].mdmp
  D. %programfiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
  A. %programfiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\Errorlog

• Question 293:

  Which of the following file systems is supported for an SQL Server 2012 installation?

  A. EXT4
  B. NTFS
  C. FAT16
  D. EXT3
  B. NTFS

  ---

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

• Question 294:

  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.

  Which Transact-SQL statement should you use?

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

  ---

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

  You administer two instances of Microsoft SQL Server. You deploy an application that uses a database on the named instance.

  The application is unable to connect to the database on the named instance.

  You need to ensure that the application can connect to the named instance.

  What should you do?

  A. Start the SQL Server Browser Service.
  B. Configure the application as data-tiered.
  C. Open port 1433 on the Windows firewall on the server.
  D. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.
  A. Start the SQL Server Browser Service.

• Question 296:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 instance.

  Users report very poor performance. You run the following query and find several wait types with high wait_time_ms_values:

  You need to resolve the high wait times to improve the performance.

  Which actions should you perform to attempt to resolve each wait type? To answer, drag the appropriate actions to the correct wait types. Answer choices may be used once, more than once, or not at all. You may need to drag the split bar

  between panes or scroll to view content.

  Select and Place:

  

  

  ---

  LOGBUFFER : Check DISK IO subsystem LOGBUFFER waits occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server. The first thought that comes to mind is to check the disk response time. PAGEIOLATCH_EX: Check DISK IO subsystem Page IO latch waits indicate slow IO response times. SOS_SCHEDULER_YIELD: Tune queries that are putting pressure on the CPU The SQL Server SOS_SCHEDULER_YIELD is a fairly common wait type and it could indicate one of two things: SQL Server CPU scheduler is utilized properly and is working efficiently There is a pressure on CPU WRITELOG: Tune queries that are putting pressure on RAM WRITELOG waits occur while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits. When SQL Server has to store data in transaction log file, it doesn't do that directly by writing the data straight on the disk where the transaction log file is stored. Instead, all data is serially written to a Log cache (often referred to as a Log buffer or Log block) which is in-memory structure.

  

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql https://www.mssqltips.com/sqlservertip/4131/troubleshooting-sql-server-transaction-log-related-wait-types/

  https://www.sqlshack.com/how-to-handle-excessive-sos_scheduler_yield-wait-type-values-in-sql-server/ https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/

• Question 297:

  You have a server named SPS1 that has Microsoft SharePoint Server 2013 installed.

  You have several Power View reports that are created in Microsoft Excel 2016.

  You need to configure SPS1 to display the Power View reports.

  Which two SQL Server components should you install on SPS1? Each correct answer presents part of the solution.

  A. SQL Server Analysis Services (SSAS) in Multidimensional and Data Mining Mode
  B. SQL Server Reporting Services (SSRS) in SharePoint mode
  C. the PowerPivot for SharePoint 2013 add-in
  D. SQL Server Analysis Services (SSAS) in SharePoint mode
  E. SQL Server Analysis Services (SSAS) in Tabular Mode
  B. SQL Server Reporting Services (SSRS) in SharePoint mode
  C. the PowerPivot for SharePoint 2013 add-in

  ---

  Power Pivot for SharePoint is a middle-tier data engine that loads, queries, and refreshes data models hosted in SharePoint. It is used to implement Power Pivot and Excel data models in SharePoint.

  Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. Power View is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services Add-in for Microsoft SharePoint Server Enterprise Edition.

  References: https://support.office.com/en-us/article/Power-View-Explore-visualize-and-present-your-data-98268d31-97e2-42aa-a52b-a68cf460472e

• Question 298:

  You plan to install a Microsoft SQL Server instance.

  The instance will support a database that has the following requirements:

  Store Excel workbooks on the file system.

  Access the workbooks through Transact-SQL.

  Include the workbooks in database backups.

  During installation, you need to ensure that the requirements will be met.

  Which feature should you use?

  A. Excel Services
  B. FILESTREAM
  C. SQL Server Integration Services (SSIS)
  D. OpenXML
  D. OpenXML

  ---

  Excel workbooks can be stored in the XML format.

  OPENXML, a Transact-SQL keyword, provides a rowset over in-memory XML documents that is similar to a table or a view. OPENXML allows access to XML data as though it is a relational rowset. It does this by providing a rowset view of

  the internal representation of an XML document. The records in the rowset can be stored in database tables.

  Incorrect:

  Not B: FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. FILESTREAM is not used for Excel workbooks.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/xml/openxml-sql-server

• Question 299:

  DRAG DROP

  You administer a Microsoft SQL Server database server that has a database named Contoso. The Contoso database has a table named ProductPrices in a schema named Sales.

  You need to create a script that writes audit events into the application log whenever data in the ProductPrices table is updated.

  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:

  

  

  ---

  Example:

  The following example creates a server audit called Payrole_Security_Audit and then a database audit specification called Payrole_Security_Audit that audits SELECT and INSERT statements by the dbo user, for the

  HumanResources.EmployeePayHistory table in the AdventureWorks2012 database.

  Copy

  USE master ;

  GO

  -- Create the server audit.

  CREATE SERVER AUDIT Payrole_Security_Audit TO FILE ( FILEPATH = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ;

  GO

  -- Enable the server audit.

  ALTER SERVER AUDIT Payrole_Security_Audit

  WITH (STATE = ON) ;

  GO

  -- Move to the target database.

  USE AdventureWorks2012 ;

  GO

  -- Create the database audit specification.

  CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables

  FOR SERVER AUDIT Payrole_Security_Audit

  ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY dbo )

  WITH (STATE = ON) ;

  GO

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql

• Question 300:

  You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012 database solution.

  You are tasked with designing a scale-out and high-availability SQL Server 2012 Online

  Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances.

  Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be indexed.

  What should you include in your solution?

  A. You should include a primary database with scheduled log shipping to the secondary database configured.
  B. You should include two servers configured in an Active-Passive SQL Server 2012 Cluster.
  C. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to the secondary database.
  D. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group.
  E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group.
  C. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to the secondary database.

  ---

  References: https://msdn.microsoft.com/en-us/library/ms151196(v=sql.110)