Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 11:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a table named Person. The structure of the table is as shown in the following exhibit. (Click the Exhibit button.)

  

  The table has the following indexes:

  A unique clustered index on the PersonID column named IX_Person_PersonID A nonclustered index on the FirstName and LastName columns named IX_Person_FirstName_LastName A nonclustered index on the PersonType column

  named IX_Person_PersonType that has FirstName and LastName as included columns The table contains approximately 700,000 records. The approximate number of records for each PersonType is 3,000.

  You execute the following query.

  SELECT P.FirstName, P.LastName

  FROM Person P

  WHERE P.PersonType = 'DR'

  You plan to analyze the performance of the query by using an execution plan. You need to ascertain that the indexes are used optimally. What should you do?

  A. Verify that a clustered index scan operation is performed on the IX_Person_PersonID index.
  B. Verify that an index seek operation is performed on the IX_Person_PersonType index.
  C. Verify that an index seek operation is performed on the IX_Person_PersonType index, and a key lookup operation is performed on the IX_Person_PersonID index.
  D. Verify that an index seek operation is performed on the IX_Person_PersonType index, and an index scan operation is performed on the IX_Person_FirstName_LastName index.
  B. Verify that an index seek operation is performed on the IX_Person_PersonType index.

• Question 12:

  You are designing a table that will store transactions for an online retailer. You anticipate that the online retailer will have an average of 500,000 transactions per day. You need to design a solution for data aging and archiving. The solution must minimize the amount of time it takes to return query results for active data.

  What should you implement?

  A. a linked server
  B. a table schema
  C. Service Broker
  D. table partitioning
  D. table partitioning

• Question 13:

  You are designing a new database. The tables of the database will be replicated to multiple offices.

  You need to design the tables to meet the following requirements:

  What should you recommend?

  A. Add a column to all the tables that have the hierarchyid data type.
  B. Add a column to all the tables that have a time data type. Configure the DEFAULT constraint to use the GETDATE () function.
  C. Add a column to all the tables that have the identity property.
  D. Add a column to all the tables that have a uniqueidentifier data type. Configure the DEFAULT constraint to use the NEWIDQ function.
  C. Add a column to all the tables that have the identity property.

• Question 14:

  You are designing a data storage solution for a transactional application.You need to ensure that each row in a table records the date and the time that the row was written. The time must be as precise as possible. Which data type should you use?

  A. datetime
  B. datetime2
  C. smalldatetime
  D. timestamp
  B. datetime2

• Question 15:

  A database contains two tables named Table1 and Table1_Details. Table1_Details contains details about the items in Table1.

  You need to ensure that when an item is removed from Table1, all related items are removed from Table1_Details.

  You must achieve this goal by using the minimum amount of Transact-SQL code.

  What should you do?

  A. Create a foreign key relationship. Set Cascade Delete to Set Null.
  B. Create a foreign key relationship. Set Cascade Delete to Cascade.
  C. Create a trigger on Table1_Details that fires on the Delete action.
  D. Create a stored procedure that deletes all related items from Table1_Detai!s.
  B. Create a foreign key relationship. Set Cascade Delete to Cascade.

• Question 16:

  You are a database developer. You plan to design a database solution by using SQL Server 2008.

  The database contains a table named SaverPlans that stores information about savings plans. The table contains a column named MinBalance that stores the minimum balance for a given plan.

  You need to ensure that each new savings plan, for which the MinBalance value is unspecified, has a minimum balance of 1,000 U.S. dollars. You want to achieve this goal by using the minimum amount of cost.

  What should you do?

  A. Use the DEFAULT constraint.
  B. Use an UPDATE trigger.
  C. Use the CHECK constraint.
  D. Use an INSERT trigger.
  D. Use an INSERT trigger.

• Question 17:

  You are a database developer. You develop a task management application that connects to a SQL Server 2008 database named TaskDB.

  Users log on to the application by using a SQL Server login. The application contains a module named Task that assigns tasks to users. Information about these tasks is stored in the Tasks table of the TaskDB database.

  The Tasks table contains multiple columns. These include the CloseDate and EstimatedTime columns.

  Users assigned to a database role named User1 can update all task information columns except the CloseDate and the EstimatedTime columns in the Tasks table.

  Administrative users assigned to a database role named Task_Admin can update all task information in the Tasks table.

  You need to design a strategy to meet the security requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

  A. Add the Task_Admin role to the db_accessadmin fixed database role.
  B. Grant Update permissions on the Tasks table to the Task_Admin role.
  C. Grant Update permissions on the Tasks table to the User1 role for each column except the CloseDate and EstimatedTime columns.
  D. Create an INSTEAD OF trigger on the Tasks Table. Use the Is_Member function to prevent the User1 role from updating the CloseDate and EstimatedTime columns.
  B. Grant Update permissions on the Tasks table to the Task_Admin role.
  C. Grant Update permissions on the Tasks table to the User1 role for each column except the CloseDate and EstimatedTime columns.

• Question 18:

  You are a database developer. You plan to design a database solution by using SQL Server 2008.

  A frequently used query takes very long to execute.

  You discover that the query frequently uses full-table scans instead of indexes. This causes other queries that modify the table to be blocked.

  The indexing strategy on the underlying tables that the query uses can change.

  You need to design a solution that performs the following tasks:

  What should you do?

  A. Use the INDEX table hint.
  B. Use the INDEX(0) table hint.
  C. Use the NOEXPAND table hint.
  D. Use the FORCESEEK table hint.
  D. Use the FORCESEEK table hint.

• Question 19:

  You need to design a database solution that meets the following capabilities: Which SQL Server component should you use?

  A. Notification Services
  B. Service Broker
  C. SQL Mail
  D. SQL Server Agent
  D. SQL Server Agent

• Question 20:

  You are a database developer. You plan to design a database solution by using SQL Server 2008.

  The database will contain a table to stage data from an external source. The imported data will contain a field named TransactionTime. The TransactionTime field will use the hh:mm:ss.[m] format (such as 12:55:30.1).

  You need to select the appropriate data type for the TransactionTime field. You want to achieve this goal by incurring minimum amount of storage cost.

  Which data type should you use?

  A. time
  B. datetime2
  C. smalldatetime
  D. time (1)
  D. time (1)