Q1. DRAG DROP 

You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition: 

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use? 

(To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) 

View Answer

Q2. 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 (XACT_STATE ( ) ) = 0 

C. IF (XACT_STATE ( ) ) = 1 

D. IF @@TRANCOUNT = l 

View Answer

Q3. You develop a database for a travel application. You need to design tables and other database objects. 

You create a view that displays the dates and times of the airline schedules on a report. 

You need to display dates and times in several international formats. 

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

Q4. You develop a Microsoft SQL Server 2012 server database that supports an application. 

The application contains a table that has the following definition: 

CREATE TABLE Inventory ( 

ItemID int NOT NULL PRIMARY KEY, 

ItemsInStore int NOT NULL, 

ItemsInWarehouse int NOT NULL) 

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. 

The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use? 

A. ALTER TABLE Inventory 

ADD TotalItems AS ItemslnStore + ItemsInWarehouse 

B. ALTER TABLE Inventory 

ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED 

C. ALTER TABLE Inventory 

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED 

D. ALTER TABLE Inventory 

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) 

View Answer

Q5. CORRECT TEXT 

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

You need to create a view named uv_CustomerFullName to meet the following requirements: 

The code must NOT include object delimiters. 

The view must be created in the Sales schema. 

Columns must only be referenced by using one-part names. 

The view must return the first name and the last name of all customers. 

The view must prevent the underlying structure of the customer table from being 

..... 

changed. 

. The view must be able to resolve all referenced objects, regardless of the user's default schema. 

Which code segment should you use? 

To answer, type the correct code in the answer area. 

View Answer

Q6. You develop a database for a travel application. You need to design tables and other database objects. 

You create the Airline_Schedules table. 

You need to store the departure and arrival dates and times of flights along with time zone 

information. 

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

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

You create a stored procedure named dbo.ModifyData that can modify rows. 

You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: 

. Does not return an error 

. Closes all opened transactions 

Which Transact-SQL statement should you use? 

A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH 

B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW; END CATCH 

C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData 

COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH 

D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH 

View Answer

Q8. You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table. 

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do? 

A. Perform a file restore. 

B. Perform a transaction log restore. 

C. Perform a restore from a full backup. 

D. Perform a filegroup restore. 

View Answer

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

<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00" Name="Customer 

A" Country="Australia" /> 

<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer 

A" Country="Australia" /> 

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, Orderld, 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') 

View Answer

Q10. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. 

You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. 

You need to ensure that the following requirements are met: 

. Future modifications to the table definition will not affect the applications' ability to access data. . The new object can accommodate data retrieval and data modification. 

You need to achieve this goal by using the minimum amount of changes to the existing applications. 

What should you create for each application? 

A. table partitions 

B. views 

C. table-valued functions 

D. stored procedures 

View Answer