Q1. You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured. 

When SQL Server Agent jobs fail, the error messages returned by the job steps are truncated. 

The following error message is an example of the truncated error message: 

"Executed as user CONTOSO\ServiceAccount....0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserveD. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution fA. .. The step failed. " 

You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs. 

What should you do? 

A. Expand agent logging to include information from all events. 

B. Disable the Limit size of job history log feature. 

C. Configure event forwarding. 

D. Configure output files. 

View Answer

Q2. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed 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 SalesOrders 

WHERE OrderTime = CONVERT(DATE, GETDATE()) 

B. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime = GETDATE() 

C. SELECT COUNT(*) FROM SalesOrders 

WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112)) 

D. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime >= CONVERT(DATE, GETDATE()) 

AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE())) 

View Answer

Q3. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column. 

You need to increase the values in the Price column of all products in the Products table by 5 percent. 

You also need to log the changes to the ProductsPriceLog table. 

Which Transact-SQL query should you use? 

A. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

B. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, inserted.Price, deleted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

C. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

D. UPDATE Products SET Price = Price * 1.05 

INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; 

SELECT ProductCode, Price, Price * 1.05 FROM Products 

View Answer

Q4. You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. 

You discover that a large amount of memory is consumed by single-use dynamic queries. 

You need to reduce procedure cache usage from these statements without creating any additional indexes. 

What should you do? 

A. Add a HASH hint to the query. 

B. Add a LOOP hint to the query. 

C. Add a FORCESEEK hint to the query. 

D. Add an INCLUDE clause to the index. 

E. Add a FORCESCAN hint to the Attach query. 

F. Add a columnstore index to cover the query. 

G. Enable the optimize for ad hoc workloads option. 

H. Cover the unique clustered index with a columnstore index. 

I. Include a SET FORCEPLAN ON statement before you run the query. 

J. Include a SET STATISTICS PROFILE ON statement before you run the query. 

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query. 

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query. 

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query. 

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query. 

View Answer

Q5. You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders. 

Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD. 

You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation. 

Which Transact-SQL command should you use? 

A. ALTER INDEX IX_OrderDateON CustomerOrderDISABLE 

B. ALTER INDEX IX_OrderDateON CustomerOrderENABLE 

C. ALTER INDEX IX_OrderDateON CustomerOrderREORGANIZE 

D. ALTER INDEX IX OrderDateON CustomerOrderREBUILD 

View Answer

Q6. You administer a Microsoft SQL Server 2012 failover cluster. 

You need to ensure that a failover occurs when the server diagnostics returns query_processing error. 

Which server configuration property should you set? 

A. SqlOumperDumpFlags 

B. FailureConditionLevel 

C. HealthCheckTimeout 

D. SqlDumperDumpPath 

View Answer

Q7. You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size. 

The media files will require fast access and will be retrieved frequently. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

View Answer

Q8. You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions: 

Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view. 

Which Transact-SQL statement should you use? 

A. CREATE TRIGGER TrgVwEmployee ON VwEmployee FOR INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END 

B. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END 

C. CREATE TRIGGER TrgVwEmployee ON VwEmployee 

INSTEAD OF INSERT 

AS 

BEGIN 

DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), 

@PersonID INT, 

@EmployeeNumber NVARCHAR(15) 

SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, 

@EmployeeNumber = 

EmployeeNumber 

FROM inserted 

INSERT INTO Person(Id, FirstName, LastName) 

VALUES(@ID, @FirstName, @LastName) 

INSERT INTO Employee(PersonID, EmployeeNumber) 

VALUES(@PersonID, @EmployeeNumber 

End 

D. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM VwEmployee End 

View Answer

Q9. 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. 

View Answer

Q10. You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA. 

Currently, Server01l/HA is the primary replicA. 

You need to ensure that the following requirements are met: 

Backup operations occur on Server02/HA. 

If Server02/HA is unavailable, backup operations occur on Server03/HA. 

Backup operations do not occur on Server01/HA. 

How should you configure HaContoso? 

A. . Set the backup preference of HaContoso to Prefer Secondary. 

. Set the backup priority of Server02/HA to 20. 

. Set the backup priority of Server03/HA to 10. 

B. . Set the backup preference of HaContoso to Secondary only. 

. Set the backup priority of Server02/HA to 20. 

. Set the backup priority of Server03/HA to 10. 

C. . Set the backup preference of HaContoso to Secondary only. 

. Set the backup priority of Server02/HA to 10. 

. Set the backup priority of Server03/HA to 20. 

D. . Set the exclude replica of Server01/HA to true. 

. Set the backup priority of Server02/HA to 10. 

. Set the backup priority of Server03/HA to 20. 

View Answer