Simulation 70-464 Discount Pack 2021

NEW QUESTION 1

You need to implement a solution that meets the security requirements. Which statement should you execute?

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

Answer: A

NEW QUESTION 2

You have a database named DB1.
You plan to configure change data capture on the existing tables in DB1.
The database file settings for the DB1 are shown in the exhibit. (Click the Exhibit button.)

You need to minimize disk contention caused by change data capture. What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. Increase the autogrowth value of the database file.
• B. Set the database recovery model to simple.
• C. Increase the autogrowth value of the log file.
• D. Configure change data capture to use to a secondary filegroup.

Answer: D

NEW QUESTION 3

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 4

You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
•Permit insertions into a table variable.
•Support structured exception handling.
•Prevent changes to the definition of referenced objects.
•Support the use of the APPLY operator on the output of the object. Which type of object should you use?

• A. An inline table-valued function
• B. A stored procedure
• C. A scalar user-defined function
• D. A multi-statement table-valued function

Answer: C

NEW QUESTION 5

You need to implement security for the restore and audit process. What should you do?

• A. Grant the COFFECORPAuditors group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permission
• B. Grant the COFFECORPStoreAgent group ALTER ANY CONNECTIONand IMPERSONATE ANY LOGIN permissions.
• C. Grant the COFFECORPAuditors group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permission
• D. Grant the COFFECORPStoreAgent group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions.
• E. Grant the COFFECORPAuditors group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permission
• F. Grant the COFFECORPStoreAgent group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions.
• G. Grant the COFFECORPAuditors group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permission
• H. Grant the COFFECORPStoreAgent group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions.

Answer: A

NEW QUESTION 6

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 7

You need to implement a solution that addresses the bulk insert requirements. What should you add to line 08 in usp_ImportOrderDetails?

• A. LASTROW=0.
• B. BATCHSIZE=0.
• C. BATCHSIZE=1000.
• D. LASTROW = 1000.

Answer: C

NEW QUESTION 8

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 9

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 10

Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains a database name DB1 and a table named Customers.
You add an additional server named SQL2 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Add SQL2 as a Distributor.
• B. Add the Customers table to SQL2.
• C. Add SQL2 as a linked server.
• D. Create the view on SQL1.
• E. Remove the Customers table from SQL1.
• F. Create the view on SQL2.

Answer: BCDF

NEW QUESTION 11

You use SQL Server 2014 to maintain the data used by applications at your company. You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second statement.
3. Error information should be returned to the client. What should you do?

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

Answer: A

Explanation:
* SET XACT_ABORT
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

NEW QUESTION 12

You plan to modify a procedure that contains hundreds of lines of code. The modification must support the following guidelines:
Use only tables that are not persistent in the database.
Minimize the amount of time required to execute and recompile procedures.
You need to identify which type of table must be used to support the planned modification.
Which type of table should you identify?

• A. A system table
• B. A partitioned table
• C. A table variable
• D. A temporary table

Answer: C

NEW QUESTION 13

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName.
What should you include in the recommendation?

• A. Change data capture
• B. Change tracking
• C. Transactional replication
• D. The Distributed Transaction Coordinator (DTC)

Answer: D

NEW QUESTION 14

You need to identify which long running transactions use an index. Which dynamic management view should you use?

• A. sys.dm_exec_query_optimizer_info
• B. sys.dm_exec_connections
• C. sys.dm_exec_query_stats
• D. sys.dm_exec_sessions

Answer: A

NEW QUESTION 15

You are testing disaster recovery procedures.
When you attempt to restore ProductsDB to another server, you receive the following error message: "Msg 33111, Level 16, State 3, Line 5
Cannot find server certificate with thumbprint ' 0x9D876A3468B911ElBA4CFCBF4724019B Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore ProductsDB to another server. Which code segment should you execute on the other server?

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

Answer: D

NEW QUESTION 16

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. Add WITH (FORCESEEK) to line 69 in usp.GetOrdersByProduct.
• C. Add WITH RECOMPILE to line 64 in usp.GetOrdersByProduct.
• D. Execute sp_recompile usp.GetOrdersByProduct'.

Answer: B

NEW QUESTION 17

You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails. For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items
in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?

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

Answer: A

NEW QUESTION 18

Your network contains a server that has SQL Server 2014 installed. You create a table by using the following script:

You need to recommend a solution to ensure that each combination of ProductName and ProductManufacturer is not duplicated.
What should you recommend creating?

• A. A UNIQUE constraint
• B. A filtered index
• C. A columnstore index
• D. A CHECK constraint

Answer: A

NEW QUESTION 19

You have a SQL Server 2012 database named Database1. You execute the following code:

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1. What should you do?

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

Answer: A

NEW QUESTION 20

You need to recommend a solution that meets the concurrency problems. What should you include in the recommendation?

• A. Modify the stored procedures to use the SERIALIZABLE isolation level.
• B. Modify the order in which usp_AcceptCandidate accesses the Applications table and the Candidatestable.
• C. Modify the order in which usp_UpdateCandidate accesses the Applications table and the Candidates table.
• D. Modify the stored procedures to use the REPEATABLE READ isolation level.

Answer: C

NEW QUESTION 21

You need to redesign the system to meet the scalability requirements of the application. 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:
* MEMORY_OPTIMIZED_DATA
First create a memory-optimized data filegroup and add a container to the filegroup. Then create a memory-optimized table.
* 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.
* Example:
-- create a durable (data will be persisted) memory-optimized table
-- two of the columns are indexed CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL, TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON) GO

NEW QUESTION 22

You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows:

Product is an important table that has sensitive audit requirements. You need to create a trigger that supports the following requirements:
1. Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate and LastUpdatedBy values in the Product table.
2. Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table.
3. Any error that occurs during the course of the trigger's execution must prevent the changes from happening. 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:
Note:
* Executing a ROLLBACK TRANSACTION or COMMIT TRANSACTION Transact-SQL statement inside a stored procedure or trigger is possible, but doing so may cause errors.

NEW QUESTION 23

You have a table named Customers that has a clustered index defined on the ID column. You write a script to create a stored procedure.
You need to complete the script for the stored procedure. The solution must minimize the number of locks and deadlocks.
What should you do?
To answer, drag the appropriate option to the correct location 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:
* Optimized bulk load operations on heaps block queries that are running under the following isolation levels: SNAPSHOT
READ UNCOMMITTED
READ COMMITTED using row versioning
* READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
* SERIALIZABLE (more locks) Specifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
* UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.
When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.
* XLOCK
Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.
Reference: Table Hints (Transact-SQL)

NEW QUESTION 24

You plan to design an application that temporarily stores data in a SQL Azure database.
You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session.
Which type of objects should you identify?

• A. Common table expressions (CTEs)
• B. Temporary stored procedures
• C. Temporary tables
• D. Table variables

Answer: C

Explanation:
http://msdn.microsoft.com/en-us/library/ms175972.aspx http://msdn.microsoft.com/en-us/library/ms189084.aspx http://msdn.microsoft.com/en-us/library/ms175010.aspx http://msdn.microsoft.com/en-us/library/bb510489.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx
http://zacksfiasco.com/post/2010/01/21/SQL-Server-Temporary-Stored-Procedures.aspx

NEW QUESTION 25
......