[Free] 2019(Nov) EnsurePass Microsoft 70-464 Dumps with VCE and PDF 31-40

Get Full Version of the Exam
http://www.EnsurePass.com/70-464.html

Question No.107

You create a table named Customers by using the following code segment:

image

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

You write the following query to retrieve all of the customers that have a name that starts with the letters SMI:

image

You discover that the query performs a table scan. You need to ensure that the query uses the index.

What should you do?

  1. Replace LEFT(name,3) = #39;smi#39; by using name like #39;smi%#39;

  2. Replace LEFT(name,3) = #39;smi#39; by using substring(name,l,3) = #39;smi#39;

  3. Recreate IX_Name as a unique index

  4. Recreate IX Name as a clustered index

Correct Answer: A

Question No.108

You plan to execute the following code:

image

A.

0

B.

1

C.

2

D.

3

You need to identify how many rows will be in dbo.Table1 after you execute the code. How many rows should you identify?

Correct Answer: A

Question No.109

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?

  1. Common table expressions (CTEs)

  2. Temporary stored procedures

  3. Temporary tables

  4. Table variables

Correct 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

Question No.110

DRAG DROP

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

image

Correct Answer:

image

Question No.111

You execute the following code:

image

You have a stored procedure that includes the following SELECT statement:

image

You need to create a covering index on UserInfo. Which code segment should you execute?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: C

Question No.112

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?

  1. STATISTICS_NORECOMPUTE = OFF

  2. FILLFACTOR = 80

  3. FILLFACTOR = 0

  4. STATISTICS_NORECOMPUTE = ON

Correct Answer: C

Explanation:

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

Question No.113

You run the following code:

image

You need to ensure that the root node of the XML data stored in the Details column is

lt;Order_Detailsgt;.

What should you implement?

More than one answer choice may achieve the goal. Select the BEST answer.

  1. A user-defined data type

  2. An XML index

  3. A Data Definition Language (DDL) trigger

  4. A data manipulation language (DML) trigger

  5. An XML schema collection

Correct Answer: E

Explanation:

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

Question No.114

You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table. What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

  1. A data manipulation language (DML) trigger

  2. A DEFAULT constraint

  3. A Data Definition Language (DDL) trigger

  4. A CHECK constraint

  5. A FOREIGN KEY constraint

Correct Answer: D

Explanation:

http://www.techrepublic.com/blog/programming-and-development/comparing-sql- serverconstraints-and-dmltriggers/402

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

Question No.115

You have a SQL Azure database. You execute the following code:

image

The Sales.Customers table will contain 100,000 rows. You expect the FaxNumber column to contain a null value for 70 percent of the rows.

You need to create an index to support Sales.CustomersByFaxNumber. The solution must minimize the disk storage requirements.

Which code segment should you execute?

  1. CREATE INDEX IX_Customers ON Customers (FaxNumber) WHERE FaxNumber IS NOT NULL

  2. CREATE INDEX IX_Customers ON Customers (FaxNumber) WITH FILLFACTOR=0

  3. CREATE INDEX IX_Customers ON Customers (CustomerName) INCLUDE (FaxNumber)

  4. CREATE INDEX IX_Customers ON Customers (FaxNumber)

  5. CREATE INDEX IX_Customers ON Customers (FaxNumber) WHERE FaxNumber IS NULL

Correct Answer: A

Question No.116

You plan to create a new column in a table. The column must meet the following requirements:

image

Be able to store images that are larger than 1 MB each.

image

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.

  1. nvarchar

  2. varbinary

  3. image

  4. FileStream

Correct Answer: D

Get Full Version of the Exam
70-464 Dumps
70-464 VCE and PDF

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.