Latest 70-464 Testing Bible 2021

NEW QUESTION 1

While testing usp.GetFutureSessions, you discover that IX_Sessions is accessed by a scan rather than a seek. You need to minimize the amount of time it takes to execute usp_GetFutureSessions.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Option A
• B. Option B
• C. Option C
• D. Option D
• E. Option E
• F. Option F

Answer: BE

Explanation:
Future delivery dates.

NEW QUESTION 2

You need to convert the functionality of Dynamic.sql to use a stored procedure. Which Transact SQL statement should you add to the stored procedure contain?

• A. CREATE PROC Production.ProductsAfterDate (@ProductID AS varchar(11), @CreationDate AS date)AS...
• B. CREATE PROC Production.ProductsAfterDate(@sqlstring AS nvarchar(1000)) AS...
• C. CREATE PROC Production.ProductsAfterDate(@sqlstring AS nvarchar(1000), OUTPUT @ProductID AS varchar(11), OUTPUT @CreationDate AS date)AS...
• D. CREATE PROC Production.ProductsAfterDate(@sqlstring AS nvarchar(1000),@ProductID AS varchar(11),@CreationDate AS date) AS...

Answer: C

Explanation:
@sqlstring, @ProductID, and @CreationData need to be declared as parameters. @ProductID, and
@CreationData should be output parameters.

NEW QUESTION 3

You have a database named database1. Each table in database1 has one index per column. Users often report that creating items takes a long time.
You need to perform the following maintenance tasks: What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: sys.dm_db_index_usage_stats
sys.dm_db_index_usage_stats shows you how many times the index was used for user queries. It returns
counts of different types of index operations and the time each type of operation was last performed in SQL Server.
Box 2: sys.dm_db_missing_index_details
sys.dm_db_missing_index_details returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx

NEW QUESTION 4

You have a database named database1.
Database developers report that there are many deadlocks.
You need to implement a solution to monitor the deadlocks. The solution must meet the following requirements:
Support real-time monitoring.
Be enabled and disabled easily.
Support querying of the monitored data. What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. Log errors by using trace flag 1222
• B. Log errors by using trace flag 1204
• C. A SQL Server Profiler template
• D. An Extended Events session

Answer: D

Explanation:
http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/08/12/monitor-deadlock-in-sql-2012/ http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlocks_2d00_in_2

NEW QUESTION 5

You are planning two stored procedures named SProc1 and SProc2. You identify the following requirements:
SProc1 must return a table.
SProc2 must return a scalar value.
You need to identify which option must be implemented for each stored procedure to return the desired data. Which options should you identify?
To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
* a table (a set of rows) can be returned through a SELECT statement
*a scalar can be returned through an output parameter.
* incorrect: TVP is used for input not output.

NEW QUESTION 6

You need to implement a solution that meets the locking requirements. Which line of code should you modify?

• A. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (UPDLOCK)
• B. Change line 09 in usp_GetOpenings to: FROM Openings o (ROWLOCK)
• C. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (READPAST)
• D. Change line 09 in usp_GetOpenings to: FROM Openings o (NOLOCK)

Answer: D

NEW QUESTION 7

You have a SQL Server 2012 database named Database1. Database1 has a data file named database1_data.mdf and a transaction log file named database1_Log.ldf. Database1_Data.mdf is
1.5 GB.
Database1_Log.ldf is 1.5 terabytes. A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log
backups in the future. Which code segment should you execute? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.
Transact-SQL
USE AdventureWorks2012; GO
-- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE; GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO
-- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
http://msdn.microsoft.com/en-gb/library/ms189493.aspx

NEW QUESTION 8

You are designing a database for a university. The database will contain two tables named Classes and StudentGrades that have the following specifications:
Classes will store brochures in the XPS format.
The brochures must be structured in folders and must be accessible byusing UNC paths.
StudentGrades must be backed up on a separate schedule than the rest ofthe database.
You need to identify which SQL Server technology meets the specifications of each table. Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
http://msdn.microsoft.com/en-us/library/gg471497.aspx http://msdn.microsoft.com/en-us/library/ff929144.aspx http://msdn.microsoft.com/en-us/library/ms189563.aspx http://msdn.microsoft.com/en-us/library/ms190174.aspx http://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION 9

You have a SQL Server 2012 database named DB1. DB1 contains four filegroups named FG1, FG2, FG3, and FG4. You execute the following code:

Two million rows are added to dbo.Sales.
You need to move the data from the first partition to a new table named SalesHistory and, starting on December 31, 2012, repartition dbo.Sales to support new sales data for three months.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
* Box 1 – Box 2:
/ You need to move the data from the first partition to a new table named SalesHistory.
/ First create the new table, then move the contents of the first partition.
*( Box 3 Box 4) Drop the partition scheme and then the partition function and the recreate them (box 5-box6). First recreate the partition function.
/You need, starting on December 31, 2012, repartition dbo.Sales to support new sales data for three months.
/ A partition function can be dropped only if there are no partition schemes currently using the partition function. If there are partition schemes using the partition function, DROP PARTITION FUNCTION returns an error.

NEW QUESTION 10

You need to create a function that filters invoices by CustomerID. The SELECT statement for the function is contained in InvoicesByCustomer.sql.
Which code segment should you use to complete the function?

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

Answer: A

NEW QUESTION 11

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.
What should you do?

• A. Execute sp_help 'usp_GetOrdersByProduct'.
• B. Execute sp_recompile 'usp_GetOrdersByProduct'.
• C. Add WITH RECOMPILE to line 03 in usp_GetOrdersByProduct.
• D. Add WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct.

Answer: C

Explanation:
Ref: http://msdn.microsoft.com/en-us/librAry/ms190439(v=sql.90).aspx

NEW QUESTION 12

You discover a sudden increase in processor utilization on a server that has SQL Server installed. You need to correlate server performance and database activity for an extended time period. Which two tools should you use? Each correct answer presents part of the solution.

• A. Activity Monitor
• B. Performance Monitor
• C. SQL Server Profiler
• D. sp_who2
• E. SQL Server Extended Events

Answer: BE

Explanation:
The Performance Monitor side, we have a few SQL Server monitoring tools AKA counters that can be used when troubleshooting CPU performance. The following counters are simple and easy to use:
Processor % Processor Time == < 80% Processor % User Time == < 80% Processor % Privileged time) == < 30% References:
https://www.sqlshack.com/sql-server-monitoring-tool-for-cpu-performance/

NEW QUESTION 13

You need to implement a solution that resolves the salary query issue. Which statement should you execute on DB1?

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

Answer: A

NEW QUESTION 14

You use the following statement to create a table.

You have the following queries.

You need to create an index to minimize the execution time of the queries.
How should you complete the statement? To answer, drag the appropriate code elements to the correct locations. Each code element 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: LastName
Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.
Box 2: FirstName
Box 3: DepartmentID
Non-key columns, called included columns, can be added to the leaf level of a nonclustered index to improve query performance by covering the query. That is, all columns referenced in the query are included in the index as either key or non-key columns. This allows the query optimizer to locate all the required information from an index scan; the table or clustered index data is not accessed.
Box 4: OfficeID

NEW QUESTION 15

You need to optimize the index structure that is used by the tables that support the fraud detection services. What should you do?

• A. Add a hashed nonclustered index to CreateDate.
• B. Add a not hash nonclustered index to CreateDate.
• C. Add a not hash clustered index on POSTransactionId and CreateDate.
• D. Add a hashed clustered index on POSTransactionId and CreateDate.

Answer: A

Explanation:
The fraud detection service will need to meet the following requirement (among others):
* Detect micropayments that are flagged with a StatusId value that is greater than 3 and that occurred within the last minute.

NEW QUESTION 16

Developers report that usp_UpdateSessionRoom periodically returns error 3960.
You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows.
What should you configure in Procedures.sql?

• A. Replace line 46 with the following code:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• B. Replace line 46 with the following code:SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
• C. Move the SELECT statement at line 49 to line 57.
• D. Move the SET statement at line 46 to line 53.

Answer: A

NEW QUESTION 17

You are planning the ManufacturingSteps table.
You need to define the ProductID column in the CREATE TABLE statement. Which code segment should you use?

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

Answer: B

Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx http://msdn.microsoft.com/en-us/library/ms179610.aspx http://msdn.microsoft.com/en-us/library/ff878370.aspx

NEW QUESTION 18

You have a SQL Server database.
The recovery model is set to full. The transaction log is backed up every night. You discover that the transaction log never decreases in size.
You execute the DBCC SHRINKFILE statement for the transaction log and you discover that the transaction log is unaffected.
You need to ensure that you can reduce the size of the transaction log. What should you do first?

• A. Truncate the transaction log
• B. Kill long-running transactions
• C. Change the recovery model to bulk-logged
• D. Perform a full backup

Answer: A

Explanation:
The transaction log must be truncated before running the DBCC SHRINKFILE operation.

NEW QUESTION 19

You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance.
The application generates all T-SQL statements dynamically and does not use stored procedures. You need to maximize the amount of memory available for data caching.
Which advanced server option should you modify?

• A. Optimize for Ad hoc Workloads
• B. Enable Contained Databases
• C. Allow Triggers to Fire Others
• D. Scan for Startup Procs

Answer: A

NEW QUESTION 20

You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a slow local disk.
You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores.
The stored procedure will use temporary storage defined as follows:

The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data.
For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

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

Answer: A

Explanation:
* You must specify a value for the BUCKET_COUNT parameter when you create the memory-optimized table. In most cases the bucket count should be between 1 and 2 times the number of distinct values in the index key. If the index key contains a lot of duplicate values, on average there are more than 10 rows for each index key value, use a nonclustered index instead
You may not always be able to predict how many values a particular index key may have or will have. Performance should be acceptable if the BUCKET_COUNT value is within 5 times of the actual number of
key values.

NEW QUESTION 21

You use SQL Azure to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1 is executing. Which transaction isolation level should you use in sp1?

• A. Repeatable read
• B. Read committed
• C. Serializable
• D. Snapshot

Answer: C

NEW QUESTION 22
......