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. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications’ ability to access data. The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing applications.
What should you create for each application? A. views
B. table partitions
C. table-valued functions
D. stored procedures
Correct Answer: A
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 develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements: Accepts a single input parameter for CustomerID.
Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)
A. CREATE PROCEDURE dbo.GetCustomerRating @Customer INT, @CustomerRatIng INT OUTPUT AS
SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers WHERE CustomerID = @CustomerID RETURN
B. EXECUTE dbo.GetCustomerRatIng 1745
C. DECLARE @customerRatingBycustomer INT DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
D. CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomeriD RETURN @Result
E. DECLARE @CustomerRatIngByCustcmer INT
EXECUTE dbo.GetCustomerRating @CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT
F. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT AS
DECLARE @Result INT SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerVaLue
WHERE Customer= = @CustomerID RETURNS @Result
Correct Answer: CF
Download Ensurepass Latest 2013 MCSA 70-461 Real Exam Questions , help you to pass exam 100%.