Accurate 70-464 Testing Material 2021

NEW QUESTION 1

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary. You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?

• A. STATISTICS_NORECOMPUTE = OFF
• B. FILLFACTOR = 80
• C. FILLFACTOR = 0
• D. STATISTICS_NORECOMPUTE = ON

Answer: C

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

NEW QUESTION 2

You have a Microsoft SQL Azure database that contains a table named Customers.
You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID of the customer is passed as an argument to the TopCustomers function.
You need to create a query that returns a list of all the Customer names and the purchase dates.
The solution must return only customers that have purchased an item during the last 12 months. What should you add to the query?

• A. OUTER JOIN
• B. CROSS JOIN
• C. CROSS APPLY
• D. OUTER APPLY

Answer: C

NEW QUESTION 3

You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.
You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?

• A. A SQL Server Profiler trace
• B. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
• C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
• D. A script that enables trace flags

Answer: A

NEW QUESTION 4

Which code segment should you use to define the ProductDetails column?

• A. ProductDetails xml (DOCUMENT Production.ProductDetailsSchema) NULL
• B. ProductDetails xml NULL
• C. ProductDetails xml (CONTENT Production.ProductDetailsSchema) NULL
• D. ProductDetails varchar(MAX) NULL

Answer: D

NEW QUESTION 5

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table. You need to minimize the number of page splits.
What should you set from the index options?

• A. FILLFACTOR = 0
• B. STATISTICS_NORECOMPUTE = OFF
• C. STATISTICS_NORECOMPUTE = ON
• D. FILLFACTOR = 80

Answer: D

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

NEW QUESTION 6

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 7

Which data type should you use for ProductType?

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

Answer: C

NEW QUESTION 8

You plan to create a new table that will contain a column named Salary. Salary will contain highly sensitive data.
Salary must meet the following requirements:
Contain numeric data.
Contain only encrypted data that remains encrypted in memory.
You need to identify which encryption type and data type must be used for Salary. Which encryption type and data type should you identity?
To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 9

You are evaluating the index design.
You need to recommend a change to Indexes.sql that will minimize the amount of time it takes for usp_AttendeesReport to execute. The solution must minimize the amount of database fragmentation.
Which line of code should you use to replace line 12 of Indexes.sql?

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

Answer: C

NEW QUESTION 10

You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf. Database1_data.mdf is 1.5 GB. Database1jog.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:

NEW QUESTION 11

You have a Microsoft SQL Azure database that contains a table named Employees.

You create a non-clustered index named EmployeeName on the name column.

You write the following query to retrieve all of the employees that have a name that starts with the letters
JOH:
You discover that the query performs a table scan.
You need to ensure that the query uses EmployeeName. What should you do?

• A. Recreate EmployeeName as a unique index
• B. Recreate EmployeeName as a clustered index
• C. Replace LEFT(name,3) = 'JOH' by using name like 'JOH%'
• D. Replace LEFT(name,3) = 'JOH' by using substring(name, 1, 3) = 'JOH'

Answer: C

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 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 14

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 15

You plan to create a stored procedure that inserts data from an XML file to the OrderDetails table. The following is the signature of the stored procedure:

The following is the XSD file used to create the ValidateOrder schema collection:

You develop a code segment that retrieves the number of items and loops through each item. Each time the loop runs, a variable named @itemNumber is incremented.
You need to develop a code segment that retrieves the product ID of each item number in the loop. Which code segment should you develop?

• A. SET @productID = @items.value'/Root/Product/productID', int)
• B. SET @productID = @items.value'/Root/Product['+ @itemNumber+ ']/@productID', int)
• C. SET @productID = @items.value'/Root/Product['+ @itemNumber+ ']/productID', int)
• D. SET @productID = @items.value'/Root/Product/@productID', int)

Answer: B

NEW QUESTION 16

An administrator provides a digital certificate named ServerCert.
You need to implement Transparent Data Encryption (TDE) on ProductsDB. Which code segment should you use?

• A. USE PRODUCTSDB; GOCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE DBCERT;GOALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
• B. USE PRODUCTSDB; GOCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT;GOALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
• C. USE PRODUCTSDB; GOCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT;GOALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
• D. USE PRODUCTSDB; GOCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE DBCERT;GOALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO

Answer: C

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

NEW QUESTION 17

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 18

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 19

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 20

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 21

You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows.
Proc1 updates data in Table1. Proc2 reads data from Table1.
You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1.
You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating.
What should you change Proc1 to do?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. Update less than 4,000 rows simultaneously.
• B. Use the PAGLOCK table hint.
• C. Wait for Proc2 to complete.
• D. Use the ROWLOCK table hint.

Answer: A

NEW QUESTION 22

You need to implement a solution that meets the job application requirements. What should you do?

• A. Create a one-to-one relationship between the Openings table and the Applications table.
• B. Create a one-to-one relationship between the Candidates table and the Applications table.
• C. Add a UNIQUE constraint to the Applications table on the ApplicationID column and CandidateID column.
• D. Add a UNIQUE constraint to the Applications table on the OpeningID column and the CandidateIDcolumn.

Answer: D

NEW QUESTION 23

You plan to deploy SQL Server 2012. You must create two tables named Table1 and Table2 that will have the following specifications:
Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
Table2 will contain a column named Column2 that is the product of two other columns in Table2.
Both Table1 and Table2 will contain more than 1 million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for the tables. Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

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

NEW QUESTION 24

You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day's activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance. You have the following requirements:
Both processes must never use more than 50 percent of the CPU at any one time.
The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.
The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
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:

Note:
CREATE WORKLOAD RESOURCE POOL
* Resource pools. A resource pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance.
* Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool.
* CAP_CPU_PERCENT =value
Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. Limits the maximum CPU bandwidth level to be the same as the specified value. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
* MIN_IOPS_PER_VOLUME =value
Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the resource pool.
* GROUP_MAX_REQUESTS =value
Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. value must be a 0 or a positive integer.

NEW QUESTION 25

You plan to create a new column in a table. The column must meet the following requirements:
Be able to store images that are larger than 1 MB each.
Be able to access the images from Microsoft .NET Framework applications. You need to recommend which data type must be used in the column.
Which data type should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. nvarchar
• B. varbinary
• C. image
• D. FileStream

Answer: D

NEW QUESTION 26
......