Exam Code: 70-461
Exam Name: Querying Microsoft SQL Server 2012
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover
that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?
A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C. ALTER INDEX ALL ON OrderDetail REBUILD
D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Correct Answer: B
You develop a Microsoft SQL Server 2012 database.
You need to create a batch process that meets the following requirements:
– Returns a result set based on supplied parameters.
– Enables the returned result set to perform a join with a table.
Which object should you use?
A. Inline user-defined function
B. Stored procedure
C. Table-valued user-defined function
D. Scalar user-defined function
Correct Answer: C
You administer a Microsoft SQL Server database that supports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution.
Choose all that apply.)
A. SELECT AccountHolderID
WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID
WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderlD
WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
D. SELECT AccountHolderID
WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)
Correct Answer: AB
You develop a database for a travel application. You need to design tables and other database objects. You
create a stored procedure. You need to supply the stored procedure with multiple event names and their dates
as parameters. What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.
Correct Answer: E
You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks:
– Joins 8 tables that contain up to 500,000 records each.
– Performs aggregations on 5 fields.
The view is frequently used in several reports.
You need to improve the performance of the reports.
What should you do?
A. Convert the view into a table-valued function.
B. Convert the view into a Common Table Expression (CTE).
C. Convert the view into an indexed view.
D. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary