Q1. You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.) 

You write the following Transact-SQL query: 

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. 

You need to improve the performance of the query. 

What should you do? 

A. Use a FORCESCAN hint in the query. 

B. Add a clustered index on SalesOrderId in SalesOrderHeader. 

C. Use a FORCESEEK hint in the query. 

D. Update statistics on SalesOrderId on both tables. 

View Answer

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

View Answer

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

View Answer

Q4. You develop a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerId. 

You need to create a query that meets the following requirements: 

. Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. . Results must not include customers who have not placed any orders. 

Which Transact-SQL query should you use? 

A. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId 

B. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId 

C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerId = Orders.CustomerId 

D. SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId 

View Answer

Q5. You administer a Microsoft SQL Server 2012 database. 

The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 

Unless stated above, no columns in the Employee table reference other tables. 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. 

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. 

On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database? 

A. DateHired 

B. Departments 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

View Answer

Q6. CORRECT TEXT 

You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements: 

. Contain a new column named LineltemTotal that stores the product of ListPrice 

and Quantity for each row. 

. The calculation for a line item total must not be run every time the table is queried. 

. The code must NOT use any object delimiters. 

The solution must ensure that LineItemTotal is stored as the last column in the table. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

View Answer

Q7. CORRECT TEXT 

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.) 

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements: 

UnitPrice must be returned in descending order. 

The query must use two-part names to reference the table. 

The query must use the RANK function to calculate the results. 

The query must return the ranking of rows in a column named PriceRank. 

The list must display the columns in the order that they are defined in the table. 

PriceRank must appear last. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

View Answer

Q8. You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns: 

Id is the Primary Key. 

You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. 

Which Transact-SQL statement should you use? 

A. UPDATE TOP(10) BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', NULL, 0) 

B. UPDATE BlogEntry 

SET Summary = CAST(N' This is in a draft stage' as nvarchar(max)) 

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) 

C. UPDATE BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM ( 

SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID 

D. UPDATE BlogEntry 

SET Summary.WRITE(N' This is in a draft stage', 0, 0) 

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) 

View Answer

Q9. You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. 

You write the following Transact-SQL query: 

. INSERT INTO OrdersHistorical 

. SELECT * FROM CompletedOrders 

You need to optimize transaction logging and locking for the statement. Which table hint should you use? 

A. HOLDLOCK 

B. ROWLOCK 

C. XLOCK 

D. UPDLOCK 

E. TABLOCK 

View Answer

Q10. You use Microsoft SQL Server 2012 to develop a database application. 

You need to implement a computed column that references a lookup table by using an INNER JOIN against another table. 

What should you do? 

A. Reference a user-defined function within the computed column. 

B. Create a BEFORE trigger that maintains the state of the computed column. 

C. Add a default constraint to the computed column that implements hard-coded values. 

D. Add a default constraint to the computed column that implements hard-coded CASE statements. 

View Answer