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

You are a database administrator on an instance of SQL Server. While creating a table, you decide to implement a sparse column. Which Transact-SQL will execute successfully and create a table with a sparse column?

You are a database developer on an instance of SQL Server 2008 named Srv1. You need to define a stored procedure that will be used by one of your applications. The stored procedure executes multiple DML statements to perform several table updates. You want to ensure that SQL Server never caches query execution plans when executing this stored procedure. Which action should you take?

You are designing a SQL Server 2008 database that the human resources department will use to store information about employees and training courses each employee has completed. You must design a database schema that will allow information about each employee's completed training to be recorded in the database, and will track completed training by department without introducing redundant data. Your data model must also meet the following requirements: Many training courses may be offered to employees. Each employee may take one or more training courses. Each training course may be taken by multiple employees. Each employee may take a specific course only once. Each employee is assigned to a single department. Departments may have multiple employees. Which actions should you take?

You are a database developer on an instance of SQL Server 2008. Your database contains the Employee table defined using the following Transact-SQL statement: CREATE TABLE dbo.Employee( EmpID int PRIMARY KEY, LastName varchar(35) NOT NULL, FirstName varchar(35) NOT NULL, HireDate datetime DEFAULT (GETDATE()), Status varchar(8) CHECK (Status IN ('Active', 'Inactive')), SSN char(9) NOT NULL, Salary money NOT NULL CHECK (Salary > 0), Commission money DEFAULT 0); You want to create a view that meets the following requirements: Users must not be able to query data for inactive employees. Users must not be able to query the SSN, Salary, or Commission column for any employees. In addition, developers must not be able to modify the structure of the Employee table such that the view becomes unusable. Which statement should you use to create the view?

You are a database developer on an instance of SQL Server 2008. Your Prod database contains a Meeting table defined using the following statement: CREATE TABLE Meeting ( ID int IDENTITY(1,1) PRIMARY KEY, Description varchar(30) NOT NULL, MaxAttendance smallint DEFAULT 0, Type bit NULL, Priority tinyint CHECK (Priority BETWEEN 0 and 10), Cost money NULL); Assuming appropriate permissions, which statement will successfully create a view?

You are a database developer. You plan to design a database solution by using SQL Server 2008. You have a database that contains a table and a table-valued function. The table-valued function accepts the primary key from the table as a parameter. You plan to write a query that joins the table to the results of the table-valued function. You need to ensure that only rows from the table that produce a result set from the table-valued function are returned. Which join predicate should you use?

You are a database developer for VirtuArt Corporation on an instance of SQL Server 2008. You are creating an application for the order processing department. The application will send e-mail notifications to sales representatives when specific orders for priority customers are shipped. When an order from a priority customer that is over $1000.00 is shipped, the application must e-mail the customer's assigned sales representative and include details of the order as an e-mail attachment, and also meet the following requirements: E-mail should be sent using Simple Mail Transfer Protocol (SMTP). Impact on the SQL server should be minimized. Which SQL Server component should you use?

Your company requires a standard code snippet to be inserted at the beginning of all stored procedures meeting the following requirements: Stored procedures should be able to call other stored procedures All stored procedures must incorporate transactions Here is the core part of the snippet DECLARE CHAR(36) @TransName = newID() IF Save Transaction @TranName ELSE Begin Transaction

You are a database administrator on an instance of SQL Server 2008. You are creating the TrxHistory table in the Sales database. You want to create a surrogate key for the table that will store a globally-unique identifier (GUID). Users at your branch offices will insert many rows into the table, and users in the accounting department at your corporate office will frequently run queries against the table. You want to minimize the size and fragmentation for the clustered primary index on the TrxHistory table. Which action should you take?

Your Prod database resides on a SQL Server 2008 instance. You have a PurchaseOrderHeader table defined as follows in the Prod database: You open a session and execute the following Transact-SQL: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM PurchaseOrderHeader; You open another session. Which statement will successfully execute in the second session with no blocking?