You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
Permit insertions into a table variable.
Support structured exception handling.
Prevent changes to the definition of referenced objects.
Support the use of the APPLY operator on the output of the object.
Which type of object should you use?

A company has a main office in London and a branch office in New York. The company’s network contains a
server named Server5 that has SQL Server installed. Server5 contains a database name ContentDB and a
table named ContentTable.
You add an additional server named Server9 that runs SQL Server.You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
Which three actions should you perform? (Each correct answer presents part of the solution. Choose all that
apply.)

You are planning two stored procedures named SProc1 and SProc2.
You identify the following requirements:
SProc1 must return a table.SProc2 must return a scalar value.
You need to identify which option must be implemented for each stored procedure to return the desired data.
Which options should you identify?
To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be
used once, more than once, or not at all.)
Select and Place:

You create a view by using the following code:

Several months after you create the view, users report that the view has started to return unexpected results.
You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results.
Which code segment should you run?

You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails.
For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items inthe OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?

You have a SQL Azure database.
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?

You have a SQL Server 2012 instance named SQL\\Instance1. Instance1 contains a database named
Database1.
You need to recommend an index defragmentation solution for an index named ContentIndex.
ContentIndex must meet the following requirements:
Remain online during the defragmentation.
Update distribution statistics.
Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one
answer choice may achieve the goal. Select the BEST answer.

You plan to create a new table that has the following requirements:
Uses a GUID data type as the primary key.
Uses a clustered index as the primary key.
Minimizes fragmentation.
You need to recommend which option to include in the CREATE statement.
Which option should you include?
More than one answer choice may achieve the goal. Select the BEST answer.

You create a table that contains the following script:

You need to prevent duplicate values in the EmployeeID field.Which five code segments should you use?
To answer, move the appropriate code segments from the list of code segments to the answer area and
arrange them in the correct order.
Select and Place:

Your network contains a SQL Server 2012 instance named SQL1. SQL1 contains a database named DB1. DB1
contains three tables.
The tables are configured as shown in the following table.

You plan to create indexes for the tables.
You need to identify which type of index must be created for each table. The solution must minimize the amount
of time required to return information from the tables.
Which type of index should you create for each table? To answer, drag the appropriate index type to the correct
table in the answer area.Select and Place: