Microsoft-Microsoft Dynamics AX 2012 Process Manufacturing Production and Logistics Set 5

You are a database developer on an instance of SQL Server. You have defined an inline table-valued function using the following statement: CREATE FUNCTION udf_GetOrderDetails (@OrderID int) RETURNS TABLE AS RETURN ( SELECT sh.OrderDate, sd.* FROM SalesOrderHeader sh INNER JOIN SalesOrderDetail sd ON sh.SalesOrderID = sd.SalesOrderID WHERE sd.SalesOrderID = @OrderID); You have table named SalesOrderHistory that contains a SalesOrderID column. You want to query the SalesOrderHistory table and use the udf_GetOrderDetails function to return order details. All sales orders in the SalesOrderHistory table and the associated order details should be displayed, even if no details exist for a given order. Which query should you use?

You are a database developer on an instance of SQL Server 2008. Your Prod database contains a POMaster table defined as follows: The Details column contains the line items for each purchase order in the following XML format: 001 25 125.57 1 002 12 29.95 0 003 100 2.25 1 You want to query the POMaster table and return the PurchaseOrderID and OrderDate. You also want the query to return the PONum and VendorName attributes from the XML stored in the Details column for the purchase order. Which query should you execute?

You manage a database in an instance of SQL Server 2008. You want to allow users to execute a given query providing different WHERE clause values. You want the solution to provide the best performance possible, but also provide maximum security. Which action should you take?

You have a table named SALES with the following columns: SalesmanID | ProductsSold 1 | 2000 2 | 4000 3 | 5000 4 | 5000 5 | 6000 6 | 6000 7 | 9000 8 | 9000 You have to chose a query that will select all SalesmanIDs that have sold an amount of products within the 3 top ProductsSold values.

You manage a database on an instance of SQL Server 2008 for a large training company. You have the following Transact-SQL: DECLARE @xmldoc xml SET @xmldoc=' Beth Jackson 35 10 Ross Moore3015Helen Lewis4530 ' You want to extract data from the XML document and insert it into a new table named EventList. The new table should contain a row for each event that includes the following columns: EventID - The ID attribute value for the event Description - The EventDesc attribute value for the event Location - The EventLoc attribute value for the event Which Transact-SQL statement should you use?

You have a database that resides on an instance of SQL Server 2008. You are creating a stored procedure that will update a critical table. The stored procedure reads data in the Master table, performs aggregation calculations on the table's data, and populates a table variable with the result. You must ensure that the variable always contains the most up-to-date information from the Master table, and you want to lock the table exclusively while the query runs. Which action should you take?

You are a database developer for a large automobile manufacturer. Users accessing a large partitioned table in the Prod database complain that their queries take a long time to complete. You run a SQL Server Profiler trace including the Lock:Escalation event, and notice that an excessive number lock escalations are occurring for the table. You would like to force SQL Server to take partition- level locks when possible to see if this improves query response time. Which action should you take?

You are a database administrator on an instance of SQL Server 2008. You want to create a table named InvTransaction that contains the following information: TranDate - A transaction date and time ProductID - A product identifier TranCOA - A transaction code corresponding to the company's chart of accounts LocationID - A warehouse location identifier BinID - A bin location identifier Quantity - A quantity TranType - A transaction type TranDetails - A detailed transaction description The data will be imported from a legacy system, and the majority of transactions will not have values to be stored in the LocationID and BinID columns. You want to minimize storage requirements for the table. What should you do?

You have a Prod database on an instance of SQL Server 2008. Your Prod database contains the following table, PurchaseOrderHistory, which contains many rows: You have two stored procedures that have the following characteristics: StoredProc1 has a default isolation level and issues multiple DML statements that perform modifications to the PurchaseOrderHistory table. StoredProc2 has a default isolation level and issues a SELECT query to return all rows in the PurchaseOrderHistory table. You want to ensure that StoredProc2 uses row versioning and retrieves the last committed version of each row. Which action should you take?

You are database developer on an instance of SQL Server 2008. The development team has recently created and compiled an assembly using a .NET Framework language. The assembly includes a method that requires access to the file system. You want to allow a user to access the method directly from Transact-SQL. Which action should you take?