Q1. You have a Microsoft SQL Azure database named DBAzure1.
You create a table in DBAzure1 by using the following script:
You need to recommend a solution to ensure that each combination of CustomerContact and CustomerDetails is not duplicated.
What should you recommend creating?
A. A CHECK constraint
B. A filtered index
C. A columnstore index
D. A UNIQUE constraint
Answer: D
Q2. 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
Q3. You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName.
What should you include in the recommendation?
A. The Distributed Transaction Coordinator (DTC)
B. Transactional replication
C. Change data capture
D. Change tracking
Answer: A
Q4. Topic 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.
Answer:
181. 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
Q5. Topic 8)
You have a table named Table1. Table1 has 1 million rows.
Table1 has a columnstore index for a column named Column1.
You need to import data to Table1. The solution must minimize the amount of time it takes
to import the data.
What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and
arrange them in the correct order.
Answer:
Q6. You need to modify the Orders table to store the XML data used by the retailers. Which statement should you execute?
A. ALTER Orders
ADD originalOrder XML (ValidateOrder);
B. ALTER Orders
ADD originalOrder XML;
C. ALTER Orders
ADD originalOrder varchar(max);
D. ALTER Orders
ADD originalOrder varbinary(max);
Answer: D
Q7. 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
Q8. Topic 7)
You need to design the UserActivity table.
Which three steps should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Q9. Topic 8)
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.
Answer:
149. You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a table that is
used for auditing is updated.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A Data Definition Language (DDL) trigger
B. A DEFAULT constraint
C. A CHECK constraint
D. A FOREIGN KEY constraint
E. A data manipulation language (DML) trigger
Q10. 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