70-462 Free Practice Test

- (Exam Topic 2)
You administer a Microsoft SQL Server database that includes a table named dbo.Log. This table contains millions of records about user activity in an application.
Records in dbo.Log that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts.
You need to be able to modify dbo.Log without requiring any changes to the applications that utilize dbo.Log. Which type of solution should you use?

1. A. Extended events
2. B. Columnstore index
3. C. Partitioned tables
4. D. Read committed snapshot

Correct Answer: C

- (Exam Topic 5)
You are planning to install a Microsoft SQL Server database server that will be used by a web application. You need to minimize the attack surface area.
Which two actions should you perform? Each correct answer presents part of the solution.

1. A. Use a single user account to start all database services.
2. B. Install only the database engine and disable other features.
3. C. Install the database server on a server core installation of Windows Server 2008 R2.
4. D. Set the database service to start automatically.
5. E. Install the database server as a named instance.

Correct Answer: BC
To reduce the attack surface area and to make sure you are not affected by undiscovered service vulnerabilities, disable any service that is not required
Because Server Core has fewer system services running on it than a Full installation does, there\'s less attack surface (that is, fewer possible vectors for malicious attacks on the server). This means that a Server Core installation is more secure than a similarly configured Full installation.
References: https://msdn.microsoft.com/en-us/library/dd184076.aspx https://msdn.microsoft.com/en-us/library/ff648664.aspx#c18618429_010

- (Exam Topic 3)
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 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.)

Solution:
Box 1: EXEC sp_configure 'show advanced options', 1; RECONFIGURE Box 2: sp_CONFIGURE ‘optimize for ad hoc workloads’,1
Box 3: RECONFIGURE
SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization
Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason a large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.
Let us now enable the option of optimizing ad hoc workload. This feature is available in all the versions of SQL Server 2008.
sp_CONFIGURE 'show advanced options',1 RECONFIGURE
GO
sp_CONFIGURE ‘optimize for ad hoc workloads’,1 RECONFIGURE
GO
SQL Server 2008 has feature of optimizing ad hoc workloads. References:
https://blog.sqlauthority.com/2009/03/21/sql-server-2008-optimize-for-ad-hoc-workloads-advance-performance-

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 4)
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?

1. A. Use the CAST function.
2. B. Use the DATE data type.
3. C. Use the FORMAT function.
4. D. Use an appropriate collation.
5. E. Use a user-defined table type.
6. F. Use the VARBINARY data type.
7. G. Use the DATETIME data type.
8. H. Use the DATETIME2 data type.
9. I. Use the DATETIMEOFFSET data type.
10. J. Use the TODATETIMEOFFSET function.

Correct Answer: C

- (Exam Topic 4)
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?

1. A. SELECT Territory, Year, Profit,LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfitFROM Profits
2. B. SELECT Territory, Year, Profit,LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfitFROM Profits
3. C. SELECT Territory, Year, Profit,LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfitFROM Profits
4. D. SELECT Territory, Year, Profit,LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfitFROM Profits

Correct Answer: C