You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an
NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the
success of a cast to a decimal (36,9). Which code segment should you use?select

You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that
will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you
include at the beginning of the stored procedure?

You have 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 include customers who have not placed any orders.
Which Transact-SQL query should you use?

You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if
the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which
Transact-SQL statement should you include at the beginning of the stored procedure?

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables
contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in
the two tables is distinct from one another. Business users want a report that includes aggregate information
about the total number of global sales and total sales amounts. You need to ensure that your query executes in
the minimum possible time. Which query should you use?

You are a database developer at an independent software vendor. You create stored procedures that contain
proprietary code. You need to protect the code frombeing viewed by your customers. Which stored procedure
option should you use?

You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word
documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which
Transact-SQL statement should you use?

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?

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. (Refer to the Exhibit.)

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?

Your database contains a table named Purchases. Thetable 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 madeon
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?