Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Topic 8)
You are planning two stored procedures named SProc1 and SProc2.
You identify the following requirements:
. SProc1 must return a table.
. SProc2 must return a scalar value. You need to identify which option must be implemented for each stored procedure to return the desired data.
Which options should you identify?
To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.)
Answer:
Q2. You are testing disaster recovery procedures.
You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111. Level 16, State 3, Line 1 Cannot find server certificate with thumbprint ,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9\ Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore DB1 to a different server.
Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
6. You need to modify InsertInvoice to comply with the application requirements. Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Q3. You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers. Customers contains more than 1 million rows.
The database has a stored procedure that was created by using the following script:
You need to ensure that up_customers returns rows when the following statement is executed:
EXECUTE up_customers'1,2,3,4,5'
What should you do?
A. Update @CustcmerTypelist to use the int data type.
B. Convert @CustomerTypeList to a table variable.
C. Convert @CustomerTypeList to an XML variable.
D. Update @CustomerTypeList to use the XML data type.
Answer: B
171. You have a SQL Azure database.
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?
A. SCHEMABINDING
B. VIEW_METADATA
C. DISTINCT
D. DEFAULT
Q4. 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?
A. Common table expressions (CTEs)
B. Temporary stored procedures
C. Temporary tables
D. Table variables
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
Q5. 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
Q6. Topic 7)
You need to implement a new version of usp_AddMobileLocation. 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:
Q7. You need to implement a solution that solves the performance issues of usp_GetOrdersAndItems.
Which statements should you execute?
A. CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate, Amount)
B. CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate) INCLUDE(Amount) WHERE ShipDate IS NULL
C. CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate, Amount) WHERE ShipDate IS NULL
D. CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate) INCLUDE( Amount)
Answer: B
Q8. 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
Q9. 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)
Q10. You have a Microsoft SQL Azure database.
You have the following stored procedure:
You discover that the stored procedure periodically fails to update HR.Employees.
You need to ensure that HR.Employees is always updated when up_employees executes.
The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?
A. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
B. Add the following line of code to line 13:
WITH (UPDLOCK)
C. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
D. Add the following line of code to line 08:
WITH (UPDLOCK)
Answer: D
Q11. Topic 7)
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.
Answer:
Q12. 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
Q13. You need to create the object used by the parameter of usp_UpdateEmployeeName.
Which code segment should you use?
A. CREATE XML SCHEMA COLLECTION EmployeesInfo
B. CREATE TYPE EmployeesInfo AS Table
C. CREATE SCHEMA EmployeesInfo
D. CREATE TABLE EmployeesInfo
Answer: B
Explanation:
Example Usage of Table-Valued Parameters (Database Engine) http://msdn.microsoft.com/en-us/library/bb510489.aspx (Benefits of using Table-Valued Parameters)
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO AdventureWorks2012.Production.Location
(Name
,CostRate
,Availability
,ModifiedDate)
SELECT *, 0, GETDATE()
FROM @TVP;
GO
Also:
http://msdn.microsoft.com/en-us/library/ms175007.aspx(CREATE TYPE *tabletypename*
AS
TABLE)
http://msdn.microsoft.com/en-us/library/ms175010.aspx(table data types)
Wrong Answers:
http://msdn.microsoft.com/en-us/library/ms174979.aspx(CREATE TABLE)
http://msdn.microsoft.com/en-us/library/ms189462.aspx(CREATE SCHEMA)
http://msdn.microsoft.com/en-us/library/ms176009.aspx(CREATE XML SCHEMA
COLLECTION)
Q14. 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
Q15. You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance.
The application generates all T-SQL statements dynamically and does not use stored procedures.
You need to maximize the amount of memory available for data caching.
Which advanced server option should you modify?
A. Optimize for Ad hoc Workloads
B. Enable Contained Databases
C. Allow Triggers to Fire Others
D. Scan for Startup Procs
Answer: A
Q16. You execute the following code. After populating the Employees table with 10,000 rows, you execute the following query:
You need to reduce the amount of time it takes to execute the query.
What should you do?
A. Partition the table and use the JobTitle column for the partition scheme.
B. Change SUBSTRING(JobTitle,l,l) = 'C to JobTitle LIKE 'c%’
C. Change SUBSTRING (JobTitle, 1,1] = 'c' to LEFT(JobTitle ,1) = 'c'.
D. Replace IX_Employees with a clustered index.
Answer: B
Q17. You have an application that uses a view to access data from multiple tables.
You need to ensure that you can insert rows into the underlying tables by using the view.
What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Define the view by using the SCHEMABINDING option.
C. Define the view by using the CHECK option.
D. Materialize the view.
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms180800.aspx http://msdn.microsoft.com/en-us/library/ms187956.aspx