Printable 70-464 Questions Pool 2021

NEW QUESTION 1

You need to create the InvoiceStatus table in DB1.
How should you define the InvoiceID column in the CREATE TABLE statement?

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

Answer: C

NEW QUESTION 2

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 3

You run the following code:

You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>. What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. A user-defined data type
• B. An XML index
• C. A Data Definition Language (DDL) trigger
• D. A data manipulation language (DML) trigger
• E. An XML schema collection

Answer: E

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

NEW QUESTION 4

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 5

You have a SQL Server 2014 database.
You plan to create a stored procedure that will retrieve the following information:
The XML content of the query plans that is stored in memory
The number of times each query plan is used
You need to identify which dynamic management objects must be used to retrieve the required information for the stored procedure.
Which dynamic management objects should you identify?
To answer, drag the appropriate dynamic management object to the correct requirement in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
* sys.dm_exec_query_plan
Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
* sys.dm_exec_cached_plans
Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

NEW QUESTION 6

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 7

You need to implement a solution that addresses the page split issues. Which statement should you execute?

• A. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (PAD_INDEX=OFF, DROP_EXISTING = ON);
• B. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (FILLFACTOR=50, DROP_EXISTING = ON);
• C. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (FILLFACTOR = 0, DROP_EXISTING = ON);
• D. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (PAD_INDEX=ON, DROP_EXISTING = ON);

Answer: B

NEW QUESTION 8

You have a Microsoft SQL Azure database named DBAzurel. DBAzurel contains a table named Orders that stores sales data.
Each order has a sales total that can only be discovered by querying multiple tables.
You need to ensure that the value of the sales total is returned by executing a query on Orders. What should you create?

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

Answer: C

Explanation:
A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters.
Incorrect:
Not A: A scalar function would only be able to use other columns from the same table.

NEW QUESTION 9

You have the following query on a disk-based table:

You discover that the query takes a long time to complete.
The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)

The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)

You need to reduce the amount of time it takes to complete the query. You must achieve this goal as quickly as possible.
What should you do?

• A. Reorganize the index.
• B. Update statistics.
• C. Create an index on LastName.
• D. Rebuild the index.

Answer: C

NEW QUESTION 10

Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2.
You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1.
What should you do?

• A. Change sp1 to run as the saUser.
• B. Grant User1 the EXECUTE permission on sp1.
• C. Add User1 to the db_datawriter role.
• D. Grant User1 the INSERT permission on Table2.

Answer: B

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

NEW QUESTION 11

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 12
You have a SQL Azure database. You execute the following script:

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2. You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?

• A. CREATE INDEX IX_Table1 ON Table1 (Column2) WITH FILLFACTOR-0
• B. CREATE INDEX IX_Table1 OK Table1 (Column1) INCLUDE (Column2)
• C. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NULL
• D. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NOT NULL

Answer: D

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

NEW QUESTION 13

You have a SQL Server 2012 instance.
You plan to create an application that uses spatial data.
You need to create an object that will support the representation of the surface area of all the oceans. Which code segment should you use?

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

Answer: A

NEW QUESTION 14

You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID.
Which line of code should you modify in CountryFromID.sql?

• A. 04
• B. 05
• C. 06
• D. 19

Answer: D

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

NEW QUESTION 15

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 16

You need to modify the stored procedure usp_LookupConcurrentUsers. What should you do?

• A. Add a clustered index to the summary table.
• B. Add a nonclustered index to the summary table.
• C. Add a clustered columnstore index to the summary table.
• D. Use a table variable instead of the summary table.

Answer: A

Explanation:
Scenario: Query the current open micropayments for users who own multiple micropayments by using a stored procedure named usp.LookupConcurrentUsers

NEW QUESTION 17

You plan to modify a stored procedure to use temporary data. The stored procedure must meet the following requirements:
Favor physical memory when physical memory is available.
Be able to roll back changes to the temporary data.
You need to recommend which object to add to the stored procedure. Which T-SQL command should you recommend?

• A. CREATE TABLE ##Table...
• B. CREATE TABLE Table...
• C. CREATE VIEW Table...
• D. CREATE PARTITION SCHEME Table...
• E. DECLARE TABLE @ Table...

Answer: A

Explanation:
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name)

NEW QUESTION 18

You plan to migrate an instance of SQL Server 2008 to a new installation of SQL Server 2012. You need to migrate alerts and e-mail notifications.
Which system stored procedures should you use? (Each correct answer presents part of the solution. Choose all that apply.)

• A. sp_syspolicy_create_job
• B. sp_add_operator
• C. sp_audit_write
• D. sp_add_alert

Answer: BC

Explanation:
B: sp_add_operator
Creates an operator (notification recipient) for use with alerts and jobs. C: sp_audit_write
Adds a user-defined audit event to the USER_DEFINED_AUDIT_GROUP. If USER_DEFINED_AUDIT_GROUP is not enabled, sp_audit_write is ignored.

NEW QUESTION 19

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 20

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 21

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 22

You are creating a stored procedure named usp1. Usp1 will create a table that will be used during the execution of usp1. Only usp1 will be allowed to access the table.
You need to write the code required to create the table for usp1. The solution must minimize the need to recompile the stored procedure.
Which code segment should you use to create the table?

• A. CREATE TABLE oneTable
• B. CREATE TABLE ##oneTable
• C. CREATE TABLE #oneTable
• D. DECLARE oneTable TABLE

Answer: B

NEW QUESTION 23

You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column that uses two-way encryption. Which code segment should you execute before you remove Column1?
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:
* Use AES_256 for two-way encryption.
* Use varbinary to store key.
* CLOSE SYMMETRIC KEY (Transact-SQL)
Closes a symmetric key, or closes all symmetric keys open in the current session.
* Example:
CREATE SYMMETRIC KEY CreditCards_Key11 WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09; GO
-- Create a column in which to store the encrypted data. ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128); GO
-- Open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09;
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted. UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
, CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID))); GO

NEW QUESTION 24

You are evaluating the index design.
You need to recommend a change to Indexes.sql that minimizes the amount of time it takes for usp_SelectEmployessByName to execute.
Which line of code should you use to replace line 08 of Indexes.sql?

• A. (EmployeeID) INCLUDE (LastName);
• B. (LastName) INCLUDE (FirstName);
• C. (LastName, EmployeeID);
• D. (FirstName, LastName);

Answer: A

Explanation:
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns? view=sql-server-2021

NEW QUESTION 25

You are creating a table to support an application that will cache data outside of SQL Server. The application will detect whether cached values were changed before it updates the values. You need to create the table, and then verify that you can insert a row into the table.
Which code segment should you use?

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

Answer: C

Explanation:
http://msdn.microsoft.com/en-us/library/ms182776.aspx
http://msdn.microsoft.com/en-us/library/ms187942.aspx http://msdn.microsoft.com/en-us/library/ms190348.aspx

NEW QUESTION 26
......