70-462 | Microsoft 70-462 Free Practice Questions 2021

NEW QUESTION 1
Which of the following is required in order for a differential backup to be useful for recovery purposes?

• A. A full backup taken previous to the differential backup.
• B. Hard drive space that is 2 times the size of the database you want to restore.
• C. Less than 1000 database transactions an hour.
• D. A full backup taken after the differential backup.

Answer: A

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

NEW QUESTION 2
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2.
Which Transact-SQL query should you use?

• A. SELECT StudentCode as Code,RANK() OVER (ORDER BY AVG (Marks) DESC) AS ValueFROM StudentMarksGROUP BY StudentCode
• B. SELECT Id, Name, Marks,DENSE_RANK() OVER (ORDER BY Marks DESC) AS RankFROMStudentMarks
• C. SELECT StudentCode as Code,DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) ASValueFROM StudentMarksGROUP BY StudentCode
• D. SELECT StudentCode as Code,NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS ValueFROM StudentMarksGROUP BY StudentCode
• E. SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode, Marks AS Marks,RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS RankFROM StudentMarks) tmpWHERE Rank = 1
• F. SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode, Marks AS Marks,RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks DESC) AS RankFROM StudentMarks) tmpWHERE Rank = 1
• G. SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode, Marks AS Marks,RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS RankFROM StudentMarks) tmpWHERE Rank = 1
• H. SELECT StudentCode AS Code,Marks AS Value FROM (SELECT StudentCode, Marks AS Marks,RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS RankFROM StudentMarks) tmpWHERE Rank = 1

Answer: D

NEW QUESTION 3
You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?

• A. Reference a user-defined function within the computed column.
• B. Create a BEFORE trigger that maintains the state of the computed column.
• C. Add a default constraint to the computed column that implements hard-coded values.
• D. Add a default constraint to the computed column that implements hard-coded CASE statements.

Answer: A

NEW QUESTION 4
On which of the following operating systems can you deploy the 64-bit version of SQL Server 2012 Enterprise edition? (Choose all that apply.)

• A. Windows 7 Ultimate (x64) edition
• B. Windows Server 2008 R2 SP1 (x64) Standard edition
• C. Windows Server 2008 SP2 (x64) Enterprise edition
• D. Windows Server 2003 R2 (x64) Enterprise edition

Answer: BC

NEW QUESTION 5
Which of the following DBCC commands shows optimizer statistics for objects such as tables?

• A. STATS
• B. SHOW_STATISTICS
• C. OBJECT_STATS
• D. OPTIMIZER

Answer: B

Explanation: References:
https://technet.microsoft.com/en-us/library/ms174384%28v=sql.110%29.aspx

NEW QUESTION 6
Which of the following SQL Server 2012 tools coordinates transactions across SQL Servers in a clustered environment?

• A. SQL Server Profiler
• B. SQL Server Query Optimizer
• C. SQL Server Management Studio
• D. Microsoft Distributed Transaction Coordinator

Answer: D

Explanation: References:
http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/15/msdtc-recommendations-on-sql-failover-cluster.aspx

NEW QUESTION 7
You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that your backup will continue if any invalid checksum is encountered. Which backup option should you use?

• A. STANDBY
• B. Differential
• C. FULL
• D. CHECKSUM
• E. BULK_LOGGED
• F. CONTINUE_AFTER_ERROR
• G. SIMPLE
• H. DBO_ONLY
• I. COPY_ONLY
• J. SKIP
• K. RESTART
• L. Transaction log
• M. NO_CHECKSUM
• N. NORECOVERY

Answer: F

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms186865.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backuprestorebase.continueaftere

NEW QUESTION 8
You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to prevent users from disabling server audits in Server01.
What should you create?

• A. An Alert
• B. A Resource Pool
• C. An Extended Event session
• D. A Policy
• E. ADatabase Audit Specification
• F. A SQL Profiler Trace
• G. A Server Audit Specification

Answer: D

NEW QUESTION 9
You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.
Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

Answer:

Explanation:

NEW QUESTION 10
You administer a Microsoft SQL Server 2012 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?

• A. Execute sp_configure 'max log size', 2G.
• B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.
• C. In SQL Server Management Studio, right-click the instance and select Database Setting
• D. Set the maximum size of the file for the transaction log.
• E. In SQL Server Management Studio, right-click the database, select Properties, and then click Files.Open the Transaction log Autogrowth window and set the maximum size of the file.

Answer: D

NEW QUESTION 11
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages. What should you do?

• A. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.
• B. Configure the AgentPortalUser user as a member of SQLAgentOperationRole in the msdb database.
• C. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser
• D. Enable the Guest user in the msdb database.

Answer: C

Explanation: To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

NEW QUESTION 12
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 2021. 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

Answer: BC

Explanation: 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-4

NEW QUESTION 13
You administer a Microsoft SQL Server database instance. You create a new user named UserA.
You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server Agent jobs owned by UserA
To which role should you add UserA?

• A. RSExecRole
• B. SQLAgentUserRole
• C. serveradmin
• D. DatabaseMailUserRole

Answer: B

NEW QUESTION 14
You administer a Microsoft SQL Server database server.
When errors that have severity level of 18 or higher occur, SQL Server must send an email message to the database administrators.
You need to configure SQL Server to send the email messages. What should you configure?

• A. Alerts and operators in SQL Server Agent
• B. An Extended Events session
• C. Policies under Policy-Based Management
• D. SQL Mail

Answer: A

Explanation: The SQL Server Agent must be configured to use Database Mail to send alerts to an operator via e-mail. References:
https://blogs.uoregon.edu/shanemcgovern/2015/09/02/database-mail-configuration-wizard-alerts-and-you/

NEW QUESTION 15
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented.
You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

• A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerIDWITH DROP EXISTING
• B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerIDREORGANIZE
• C. ALTER INDEX ALL ON OrderDetailREBUILD
• D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerIDREBUILD

Answer: B

NEW QUESTION 16
You administer a Microsoft SQL Server 2012 server that has multiple databases.
You need to ensure that users are unable to create stored procedures that begin with sp_.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Answer:

Explanation: Step 1: Create a Condition named StoredProcNamingConvention by using the Stored Procedure facet that has a single expression. Set the Field to @Name, Operator to NOT LIKE, and Value to 'sp[_]%'.
Step 2: Create a Policy named StoredProcNamingPolicy Set the Check condition to StoredProcNamingConvention and Evaluation Mode to On Change: Prevent
Step 3: Enable StoredProcNamingPolicy
Policies are created and managed by using Management Studio. The process includes the following steps:
Select a Policy-Based Management facet that contains the properties to be configured.
Define a condition that specifies the state of a management facet.
Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.
Check whether an instance of SQL Server is in compliance with the policy. Evaluation modes
There are four evaluation modes, three of which can be automated:
On demand. This mode evaluates the policy when directly specified by the user.
On change: prevent. This automated mode uses DDL triggers to prevent policy violations.Important:If the nested triggers server configuration option is disabled, On change: prevent will not work correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.
On change: log only. This automated mode uses event notification to evaluate a policy when a relevant change is made.
On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.

NEW QUESTION 17
You administer a Microsoft SQL Server 2012.
A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

• A. EXEC sp_who 60
• B. SELECT * FROM sys.dm_exec_sessions WHERE sessionjd = 60
• C. EXEC sp_helpdb 60
• D. DBCC INPUTBUFFER (60)

Answer: A

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms174313.aspx http://msdn.microsoft.com/en-us/library/ms176013.aspx http://msdn.microsoft.com/en-us/library/ms178568.aspx http://msdn.microsoft.com/en-us/library/ms187730.aspx

NEW QUESTION 18
You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?

• A. Use the CAST function.
• B. Use the DATE data type.
• C. Use the FORMAT function.
• D. Use an appropriate collation.
• E. Use a user-defined table type.
• F. Use the VARBINARY data type.
• G. Use the DATETIME data type.
• H. Use the DATETIME2 data type.
• I. Use the DATETIMEOFFSET data type.
• J. Use the TODATETIMEOFFSET function.

Answer: C