http://https://www.newpassleader.com/Microsoft/70-461-exam-preparation-materials.html (165 Q&As Dumps, 30%OFF Special Discount: 30free )

NEW QUESTION NO: 8
Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId.
The data in the two tables is distinct from one another.
Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.
You need to ensure that your query executes in the minimum possible time.
Which query should you use?
A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount
FROM DomesticSalesOrders
UNION
SELECT SalesOrderId, SalesAmount
FROM InternationalSalesOrders
) AS p
C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders
D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM InternationalSalesOrders
Answer: A
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms180026.aspx
Reference: http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all- optimalperformance-comparison/

NEW QUESTION NO: 9
Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column.
The business team wants a report that displays the total number of purchases made on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
A. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = GETDATE()
C. SELECT COUNT(*)
FROM Purchases
WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(),
112)
D. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
Answer: D
Explanation/Reference:
Explanation:
Two answers will return the correct results (the "WHERE CONVERT..." and "WHERE ... AND ... " answers).
The correct answer for Microsoft would be the answer that is most "efficient". Anybody have a clue as to which is most efficient? In the execution plan, the one that I've selected as the correct answer is the query with the shortest duration. Also, the query answer with "WHERE CONVERT..." threw warnings in the execution plan...something about affecting CardinalityEstimate and SeekPlan.
http://technet.microsoft.com/en-us/library/ms181034.aspx

NEW QUESTION NO: 10
You develop a Microsoft SQL Server database. You create a view from the Orders and OrderDetails tables by using the following definition.

You need to ensure that users are able to modify data by using the view.
What should you do?
A. Create an AFTER trigger on the view.
B. Modify the view to use the WITH VIEW_METADATA clause.
C. Create an INSTEAD OF trigger on the view.
D. Modify the view to an indexed view.
Answer: C
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION NO: 11
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution.
Choose all that apply.)
A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.
B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modifythe stored procedure to include the EXECUTE AS {newlogin} statement.
D. Grant the db_owner role on the database to User1.
E. Grant the sysadmin role on the database to User1.
Answer: D,E
Explanation/Reference:
Explanation:

NEW QUESTION NO: 12
You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.
The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view.
What should you do?
A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
C. Create an AFTER UPDATE trigger on the view.
D. Create an INSTEAD OF UPDATE trigger on the view.
Answer: D
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION NO: 13
You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema.
You need to achieve this goal by using the minimum amount of administrative effort. What should you do?
A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriterrole. Add the users to the db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.
Answer: A
Explanation/Reference:
Explanation:

NEW QUESTION NO: 14
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
Answer: B
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms188388.aspx

NEW QUESTION NO: 15
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.

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: C
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms188273.aspx

NEW QUESTION NO: 16
DRAG DROP
You use a Microsoft SQL Server 2012 database.
You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.
Which four T-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: 

Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms191432.aspx
http://stackoverflow.com/questions/12419330/how-to-create-indexed-view-with-select-distinct-statement- insql-2005

NEW QUESTION NO: 17
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: 18
You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?
A. SQL user without login
B. SQL user with a custom SID
C. SQL user with login
D. Domain user
Answer: A
Explanation/Reference:
Explanation: