Up To The Minute 70-464 Interactive Bootcamp 2021

NEW QUESTION 1

You are planning two stored procedures named SProc1 and SProc2. You identify the following requirements:
SProc1 must return a table.
SProc2 must return a status code.
You need to identify which options must be implemented to meet each stored procedure requirement. 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:

NEW QUESTION 2

You have a table named Table1 that stores customer data.
Each customer has a credit limit that can only be discovered by querying multiple tables.
You need to ensure that the value of the credit limit is returned by executing a query on Table1. What should you create?

• A. A trigger that uses a ranking function
• B. A trigger that uses a table-valued function
• C. A calculated column that uses a table-valued function
• D. A calculated column that uses a scalar function

Answer: C

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:
Identify unused indexes.
Identify indexes that need to be defragmented. 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:
Note:
* sys.dm_db_index_usage_stats
Returns counts of different types of index operations and the time each type of operation was last performed.
* sys.dm_db_index_physical_stats
Returns size and fragmentation information for the data and indexes of the specified table or view.

NEW QUESTION 4

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 5

You create a view by using the following code:

Several months after you create the view, users report that the view has started to return unexpected results. You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results. Which code segment should you run?

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

Answer: D

NEW QUESTION 6

You have a SQL Server 2012 instance named SQLInstance1. Instance1 contains a database named Database1. You need to recommend an index defragmentation solution for an index named ContentIndex. ContentIndex
must meet the following requirements:
Remain online during the defragmentation.
Update distribution statistics.
Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

• A. DBCC DBREINDEX
• B. REORGANIZE
• C. REBUILD
• D. DBCC INDEXDEFRAG

Answer: B

NEW QUESTION 7

You have a SQL Server 2012 database named DB1. You have a backup device named Device1. You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?

• A. BACKUP LOG DB1 TO Device1 WITH COPY_ONLY
• B. BACKUP LOG DB1 TO Device1 WITH NORECOVERY
• C. BACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY
• D. BACKUP LOG DB1 TO Device1

Answer: D

Explanation:
http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/ms179478.aspx http://msdn.microsoft.com/en-us/library/ms190925.aspx

NEW QUESTION 8

The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values.
You need to update Fax to meet the following requirements:
Prevent null values from being used.
Always use an empty string instead of a null value.
Which statement or statements should you execute? (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

Answer: ABE

Explanation:
E: First change the NULLs to ' '.
A: Then set the default to the column to ' '.
B: Finally add the NOT NULL constraint to the column.

NEW QUESTION 9

Which data type should you use for ProductType?

• A. varchar(11)
• B. nvarchar(11)
• C. char(11)
• D. bigint

Answer: C

NEW QUESTION 10

You need to create the object used by the parameter of usp_UpdateEmployeeName. Which code segment should you use?

• A. CREATE XML SCHEMA COLLECTION EmployeesInfo
• B. CREATE TYPE EmployeesInfo AS Table
• C. CREATE SCHEMA EmployeesInfo
• D. CREATE TABLE EmployeesInfo

Answer: B

Explanation:
Example Usage of Table-Valued Parameters (Database Engine)
http://msdn.microsoft.com/en-us/library/bb510489.aspx
(Benefits of using Table-Valued Parameters)
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50)
, CostRate INT ); GO
/* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation
@TVP LocationTableType READONLY AS
SET NOCOUNT ON
INSERT INTO AdventureWorks2012.Production.Location (Name
,CostRate
,Availability
,ModifiedDate)
SELECT *, 0, GETDATE() FROM @TVP;
GO
Also:
http://msdn.microsoft.com/en-us/library/ms175007.aspx(CREATE TYPE *tabletypename* AS TABLE)
http://msdn.microsoft.com/en-us/library/ms175010.aspx(table data types)
Wrong Answers:
http://msdn.microsoft.com/en-us/library/ms174979.aspx(CREATE TABLE) http://msdn.microsoft.com/en-us/library/ms189462.aspx(CREATE SCHEMA) http://msdn.microsoft.com/en-us/library/ms176009.aspx(CREATE XML SCHEMA COLLECTION)

NEW QUESTION 11

You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_GetOrdersAndItems?

• A. Add SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 03.
• B. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 03.
• C. Add (UPDLOCK) to the end of line 06.
• D. Add (READPAST) to the end of line 06.

Answer: D

NEW QUESTION 12

You need to optimize the index and table structures for POSTransaction.
Which task should you use with each maintenance step? To answer, drag the appropriate tasks to the correct maintenance steps. Each task 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:

NEW QUESTION 13

You need to modify InsertInvoice to comply with the application requirements. Which code segment should you execute?

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

Answer: D

NEW QUESTION 14

You need to create the usp.AssignUser stored procedure.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:

Box 2:

Box 3:

Box 4:

Box 5:

Box 6:

Box 7:

Note:
* From scenario: The mobile application will need to meet the following requirements:
/Communicate with web services that assign a new user to a micropayment by using a stored procedure named usp_AssignUser.
* Example:
create procedure dbo.OrderInsert(@OrdNo integer, @CustCode nvarchar(5))
with native_compilation, schemabinding, execute as owner as
begin atomic with
(transaction isolation level = snapshot, language = N'English')
declare @OrdDate datetime = getdate();
insert into dbo.Ord (OrdNo, CustCode, OrdDate) values (@OrdNo, @CustCode, @OrdDate); end
go
* Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
* READ COMITTED versus REPEATABLE READ
Read committed is an isolation level that guarantees that any data read was committed at the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. IT makes no promise whatsoever that if the transaction re-issues the read, will find the Same data, data is free to change after it was read.
Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.
* Both RAISERROR and THROW statements are used to raise an error in Sql Server.
The journey of RAISERROR started from Sql Server 7.0, where as the journey of THROW statement has just began with Sql Server 2012. obviously, Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR.
* Explicit transactions. The user starts the transaction through an explicit BEGIN TRAN or BEGIN ATOMIC. The transaction is completed following the corresponding COMMIT and ROLLBACK or END (in the case of an atomic block).

NEW QUESTION 15

You have a SQL Server 2012 database named database1.
Users report that queries that usually take less than one second to execute, take more than 30 seconds to
execute.
You need to view the server resource consumption when the queries are executed. What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: Start a SQL Server Profiler trace.
Box 2: Start a data collection by using Performance monitor. Box 3: Save the SQL Profiler trace.
Box 4: Save the Performance Monitor data.
Box 5: Import the performance data into SQL Server Profiler. Note:
* (step1, step 2) Both the Profiler trace and the Performance Monitor logs should be started and stopped at
about the same time.
* (step 3, step 4) Once you have completed capturing the data for both tools, you are ready to perform the correlation analysis.
* (step 5) How to Correlate SQL Server Profiler Data with Performance Monitor Data
Correlating Performance Monitor and Profiler data is a straightforward process that simply involves importing both sets of data into Profiler. Start Profiler and load the trace file you want to correlate.
From the main menu of Profiler, select File | Import Performance Data,
* With SQL Server Profiler, we have the tools to identify the causes of such spikes. We can import Performance Monitor log data and compare it directly with Profiler activity. If we see a spike in CPU utilization, we can identify which statement or statements were running at the same time, and diagnose potential problems.

NEW QUESTION 16

You have an application that uses a view to access data from multiple tables.
You need to ensure that you can insert rows into the underlying tables by using the view. What should you do?

• A. Create an INSTEAD OF trigger on the view.
• B. Define the view by using the SCHEMABINDING option.
• C. Define the view by using the CHECK option.
• D. Materialize the view.

Answer: C

Explanation:
http://msdn.microsoft.com/en-us/library/ms180800.aspx http://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION 17

You execute usp_TestSpeakers.
You discover that usp_SelectSpeakersByName uses inefficient execution plans.
You need to update usp_SelectSpeakersByName to ensure that the most efficient execution plan is used. What should you add at line 30 of Procedures.sql?

• A. OPTION (FORCESCAN)
• B. OPTION (FORCESEEK)
• C. OPTION (OPTIMIZE FOR UNKNOWN)
• D. OPTION (OPTIMIZE FOR (@LastName= 'Anderson'))

Answer: C

Explanation:
http://msdn.microsoft.com/en-us/library/ms181714.aspx

NEW QUESTION 18

You discover that the usp_GetOrdersAndItems stored procedure takes a long time to complete while usp_AddOrder or usp_AddXMLOrder run.
You need to ensure that usp_GetOrdersAndItems completes as quickly as possible.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SERIALIZABLE.
• B. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON statement.
• C. Set the isolation level of the usp_AddOrder stored procedure to SERIALIZABLE.
• D. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SNAPSHOT.
• E. Set the isolation level of the usp_AddOrder stored procedure to SNAPSHOT.
• F. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION OFF statement.

Answer: BD

NEW QUESTION 19

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 20

You have database objects that were created by using the following script:

The dbo.Customers table has 1 million rows.
You discover that usp_GetCustomersByDate takes a long time to complete.
The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit button.)

You need to ensure that usp_GetCustomersByDate completes as quickly as possible. What should you do?

• A. Modify the stored procedure to include the OPTIMIZE FOR UNKNOWN query hint.
• B. Execute the sp_recompile 'dbo.GetCustomersByDate' statement.
• C. Execute the ALTER INDEXIX_Customers_CreationDate WITH REBUILD statement.
• D. Modify the stored procedure to include the OPTIMIZE FOR('1/1/2008') query hint.

Answer: A

NEW QUESTION 21

You run the following code segment:

After you add 10,000 rows to Customers, you discover that the index is fragmented. You need to defragment the index in the least amount of time.
Which code segment should you execute?
To answer, drag the appropriate value to the correct location in the code segment 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:
Locking the table during the process and not recomputing statistics would be the fastest.
* Online = OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
* STATISTICS_NORECOMPUTE = ON
Out-of-date statistics are not automatically recomputed. Reference: ALTER INDEX (Transact-SQL)

NEW QUESTION 22

You need to resolve the performance issues of the usp_getOpenings stored procedure. Which three actions should you perform? Each correct answer presents part of the solution.

• A. Delete lines 05 through 08
• B. Replace lines 12, 13, and 14 with the Transact-SQL segment:WHERE (CONTAINS(o.Description, 'ISABOUT(' +@keyword+' weight (.5))'))
• C. Create a full text index on the Description column
• D. Replace lines 12, 13, and 14 with the Transact_SQL segment: WHERE (CONTAINS(o.Description, @keyword))
• E. Replace lines 12, 13, and 14 with the Transact SQL Segment:WHERE (Contains(o.Description, 'FORMSOF(INFLECTIONAL, '+@keyword+')'))

Answer: ACE

Explanation:
Scenario: You also discover that the usp_GetOpenings stored procedure takes a long time to run and that the non-clustered index on the Description column is not being used.
The FORMSOF term performs matches using other linguistic forms of the word. The following is the FORMSOF term syntax:
FORMSOF (<generation_type>,<match_words>)
The generation type specifies how Microsoft Windows Search chooses the alternative word forms. The INFLECTIONAL value chooses alternative inflection forms for the match words. If the word is a verb, alternative tenses are used. If the word is a noun, the singular, plural, and possessive forms are used to detect matches.
References:
https://docs.microsoft.com/en-us/windows/win32/search/-search-sql-formsof

NEW QUESTION 23

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 24
......