Ensurepass

QUESTION 1
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
Correct Answer: B
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms188354.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms187926.aspx

QUESTION 2
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. User mapped to a certificate
B. SQL user without login
C. Domain user
D. SQL user with login
Correct Answer: C
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ff929071.aspx

QUESTION 3
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_denydatawriter role. 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.
Correct Answer: A
Explanation/Reference:
Looks good.

QUESTION 4
You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:
You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?
A. Convert all indexes to Column Store indexes. B. Implement Unicode Compression.
C. Implement row-level compression. D. Implement page-level compression.
Correct Answer: D
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/cc280449.aspx Reference: http://msdn.microsoft.com/en-us/library/cc280464.aspx Reference: http://msdn.microsoft.com/en-us/library/cc280576.aspx Reference: http://msdn.microsoft.com/en-us/library/ee240835.aspx

QUESTION 5
You generate a daily report according to the following query:
You need to improve the performance of the query. What should you do? A. Drop the UDF and rewrite the report query as follows:
WITH cte(CustomerID, LastOrderDate) AS (
SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]
FROM Sales.SalesOrder
GROUP BY CustomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c
ON cte.CustomerID = c.CustomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE()) B. Drop the UDF and rewrite the report query as follows: SELECT c.CustomerName FROM Sales.Customer c WHERE NOT EXISTS ( SELECT s.OrderDate FROM Sales.SalesOrder WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)
C. Drop the UDF and rewrite the report query as follows:
SELECT DISTINCT c.CustomerName
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder s
ON c.CustomerID = s.CustomerID
WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE()) D. Rewrite the report query as follows: SELECT c.CustomerName FROM Sales.Customer c WHERE NOT EXISTS (SELECT OrderDate FROM Sales.ufnGetRecentOrders(c. CustomerID, 90)) Rewrite the UDF as follows: CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime) RETURNS TABLE AS RETURN ( SELECT OrderDate FROM Sales.SalesOrder WHERE s.CustomerID = @CustomerID AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())
Correct Answer: A
Explanation/Reference:

Download Ensurepass Latest 2013 MCSA 70-461 Real Exam Questions , help you to pass exam 100%.

Comments are closed.