Free Microsoft 70-457 Exam Dumps Questions & Answers

You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)

The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

• A.IF @@TRANCOUNT = 0
• B.IF @@TRANCOUNT = 1
• C.IF (XACT_STATE ( ) ) = 0
• D.IF (XACT_STATE ( ) ) = 1

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. You
attempt to add the [Corpnet\User1] login to the database. However, you receive the following error
message:
"User already exists in current database."
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the
original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?

• A.DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];
• B.ALTER SERVER ROLE [sysadmin] ADD MEMBER [Corpnet\User1];
• C.ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
• D.ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to be notified immediately when fatal errors occur on Server01. What should you create?

• A.a Policy
• B.a Database Audit Specification
• C.a Resource Pool
• D.a SQL Profiler Trace
• E.a Server Audit Specification
• F.an Extended Event session
• G.an Alert

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())
  AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
• B.SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
• C.SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()
• D.SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = CONVERT(DATE, GETDATE())

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between
09:00
hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. These data load operations must occur in the minimum amount of time. A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that the minimum amount of data is lost. Which recovery model should the database use?

• A.SIMPLE
• B.CONTINUE_AFTER_ERROR
• C.FULL
• D.NO_CHECKSUM
• E.SKIP
• F.DBO_ONLY
• G.BULK_LOGGED
• H.STANDBY
• I.Differential
• J.CHECKSUM
• K.Transaction log
• L.COPY_ONLY
• M.NORECOVERY
• N.RESTART