http://https://www.lead1pass.com/Microsoft/70-461-practice-exam-dumps.html (204 Q&As Dumps, 30%OFF Special Discount: 30free )

NEW QUESTION NO: 15
You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT CustomerID FROM Customer
WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
B. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
C. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
D. SELECT CustomerID FROMCustomer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)
Answer: B,D
Explanation/Reference:
Explanation:

NEW QUESTION NO: 16
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?
A. SET XACT_ABORT ON
B. SET ARITHABORT ON
C. TRY
D. BEGIN
E. SET ARITHABORT OFF
F. SET XACT_ABORT OFF
Answer: A
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms190306.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms188792.aspx

NEW QUESTION NO: 17
You use Microsoft SQL Server 2012 to develop a database application.
You need to create an object that meets the following requirements:
Takes an input variable

Returns a table of values

Cannot be referenced within a view

Which object should you use?
A. Scalar-valued function
B. Inline function
C. User-defined data type
D. Stored procedure
Answer: D
Explanation/Reference:
Explanation:

NEW QUESTION NO: 18
DRAG DROP
You create the following stored procedure. (Line numbers are included for reference only.)

You need to ensure that the stored procedure performs the following tasks:
If a record exists, update the record.

If no record exists, insert a new record.

Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) Select and Place:

Answer: 

Explanation/Reference:
Reference: http://technet.microsoft.com/en-us/library/bb510625.aspx

NEW QUESTION NO: 19
You use Microsoft SQL Server 2012 to develop a database application.
You create a stored procedure named dbo.ModifyData that can modify rows.
You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:
Does not return an error

Closes all opened transactions

Which Transact-SQL statement should you use?
A. BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0
ROLLBACK TRANSACTION;
END CATCH
B. BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
C. BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
D. BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
END CATCH
Answer: D
Explanation/Reference:
Explanation:

NEW QUESTION NO: 20
You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).
Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?
A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
C. Change the Merge agent on the problem subscribers to run continuously.
D. Set the Remote Connection Timeout on the Publisher to 0.
Answer: A
Explanation/Reference:
Explanation:

NEW QUESTION NO: 21
You are a database developer of a Microsoft SQL Server 2012 database.
The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table.
Which Transact-SQL statement should you use?
A. ALTER TABLE Orders
ADD CONSTRAINTFX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES
Customer (CustomerId)
B. ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID) REFERENCES Orders (CustomerId)
C. ALTER TABLE Orders
ADD CONSTRAINT CK_Orders_CustomerID
CHECK (CustomerId IN (SELECT CustomerId FROM Customer))
D. ALTER TABLE Customer
ADD OrderId INT NOT NULL;
ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);
E. ALTER TABLE Orders
ADD CONSTRAINT PKOrders CustomerId PRIMARY KEY (CustomerID)
Answer: A
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms189049.aspx

NEW QUESTION NO: 22
You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?
A. GRANT DELETE ON Purchases.Suppliers TO ContosoUser
B. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
WITH EXECUTE AS USER = 'dbo'
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
D. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
Answer: D
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms188354.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms187926.aspx

NEW QUESTION NO: 23
DRAG DROP
You use Microsoft SQL Server to develop a database application.
You create two tables by using the following table definitions.

Which five Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.) Select and Place:

Answer: 


NEW QUESTION NO: 24
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to
1 in the following XML format:
<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00" Name="Customer A" Country="Australia" />
<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer A" Country="Australia" /> Which Transact-SQL query should you use?
A. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW
B. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS
C. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
D. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS
E. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
F. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS
G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate,
Amount
FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')
H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country',
OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')
Answer: A
Explanation/Reference:
Reference:http://msdn.microsoft.com/en-us/library/bb510464.aspx

NEW QUESTION NO: 25
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
Answer: C
Explanation/Reference:
Explanation: