You have to store windows media file (WMV) that has size greater that 2GB in the database.

What datatype should you chose?

You have two offices, one in NY and one in Tokyo. The Main database is located in the NY office.

You must permit the Tokyo sales manager to produce local reports in order to reduce WAN traffic. Which option will best work?

There’re two stored procedures in the database. The stored procedures are respectively named AlterSelling and GetSelling. Every day the two procedures are executed very often. The AlterSelling stored procedure updates a table named SellingOrders by using a transaction. From the SellingOrders table, data is retrieved and aggregated by the GetSelling stored procedure for a sales trend analysis report. When users run the sales trend analysis report, they have to wait for a long time. So what should you do to make sure that sales trend analysis report runs as quickly as possible?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a table that has a parent-child relationship to itself. Each child might also be a parent. This might exist up to 10 levels deep. You need to retrieve all levels by using a single Transact-SQL query. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a table named Employee_Vacation. You are given an updated list of employee vacations used. The list is in an XML formatted file. The extract of the XML format is written in the following manner:

<Company Name ="ABC Company Pvt Ltd">
<EmployeeLeave>
<Employee ID = "1" Name="Jim Reeves" />
<Leaves>
<Leave Date="2008-02-12" />
<Leave Date="2008-02-13" />
<Leave Date="2008-02-14" />
</Leaves>
</EmployeeLeave>

You plan to import the data and update the Employee_Vacation table. You design a query to calculate the number of vacation days used by each employee. You need to ensure that vacation days are accurately counted for each employee. What should you do?

You are a SQL Server 2008 developer. You create an online transaction processing (OLTP) database by using SQL Server 2008 in an enterprise environment. The database contains a table named SalesDetails. Each record in the table contains data in any one of the following pairs of nullable columns:
* InternetSalesTargets and InternetSales
* ResellerSalesTargets and ResellerSales
* ForeignSalesTargets and ForeignSales
The table also contains three NOT NULL key columns. A large number of records are inserted on a daily basis into the SalesDetails table. Summary reports are generated from the SalesDetails table. Each report is based on aggregated data from any one of the pairs of nullable columns. You need to design a view or views to meet the following requirements:
* The SalesDetails table cannot be directly modified.
* The performance of the reports is maximized.
* The amount of storage space for each report is minimized.
What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. You create a stored procedure that uses the TRY/CATCH syntax in a new database. When the stored procedure is executed, it logs information about each step in the TRY block into a table named dbo.ExecutionLog. When an error occurs, the stored procedure must perform the following tasks:
* Roll back the changes made to the target tables.
* Retain the log entries stored in the dbo.ExecutionLog table.
You need to ensure that the stored procedure performs the given tasks. What should you do?

A common language runtime (CLR) user-defined scalar function will be contained in the database. An integer value will be returned by the function. What should you do to make sure that the computed columns that use the result from this function can be indexed?

The database will contain a table. Customer data are stored in the table as XML data. The data supports an application that cannot be altered. You plan to prevent the types of errors below in the XML data:
* Invalid values in the Gender field,
* NULL values in the Customer Name field,
* Non-numeric values in the Customer Telephone field.
What should you do to implement the plan while not modifying the application?

Data from two storerooms are contained in the database in a table named Manufacures. The two storerooms are respectively called Storeroom1 and Storeroom2. And the database supports a warehousing application. A storeroom indicator field named storeroom_id is contained in the Manufacture table. Storeroom1 includes 275,000 items and Storeroom2 includes 55,000 items. A third-party application is used by the solutions. And the third-party application runs on SQL Server 2008 and uses a stored procedure that returns the storeroom inventory based on the warehouse_id parameter. You notice that when users query the storeroom1 inventory, users sometimes experience poor system performance. But the stored procedures cannot be modified in the application. So when the inventory of Storeroom1 is quried, what should you do to ensure the system performance?