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:
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:
You discover that the query performs a table scan. You need to ensure that the query uses the index.
What should you do?
-
Replace LEFT(name,3) = #39;smi#39; by using name like #39;smi%#39;
-
Replace LEFT(name,3) = #39;smi#39; by using substring(name,l,3) = #39;smi#39;
-
Recreate IX_Name as a unique index
-
Recreate IX Name as a clustered index
Correct Answer: A
Question No.108
You plan to execute the following code:
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?
-
Common table expressions (CTEs)
-
Temporary stored procedures
-
Temporary tables
-
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.)
Correct Answer:
Question No.111
You execute the following code:
You have a stored procedure that includes the following SELECT statement:
You need to create a covering index on UserInfo. Which code segment should you execute?
-
Option A
-
Option B
-
Option C
-
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?
-
STATISTICS_NORECOMPUTE = OFF
-
FILLFACTOR = 80
-
FILLFACTOR = 0
-
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:
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.
-
A user-defined data type
-
An XML index
-
A Data Definition Language (DDL) trigger
-
A data manipulation language (DML) trigger
-
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.
-
A data manipulation language (DML) trigger
-
A DEFAULT constraint
-
A Data Definition Language (DDL) trigger
-
A CHECK constraint
-
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:
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?
-
CREATE INDEX IX_Customers ON Customers (FaxNumber) WHERE FaxNumber IS NOT NULL
-
CREATE INDEX IX_Customers ON Customers (FaxNumber) WITH FILLFACTOR=0
-
CREATE INDEX IX_Customers ON Customers (CustomerName) INCLUDE (FaxNumber)
-
CREATE INDEX IX_Customers ON Customers (FaxNumber)
-
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:
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.
-
nvarchar
-
varbinary
-
image
-
FileStream
Correct Answer: D
Get Full Version of the Exam
70-464 Dumps
70-464 VCE and PDF