Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 91:

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

  Your company has a main office in New York and several branch offices globally. All the offices use databases on their local SQL Server 2008 servers to support trading applications. A low-bandwidth WAN link connects the offices. The WAN

  link is subject to interruptions.

  You plan to implement a reporting instance that will be deployed in the main office.

  You need to design a solution to transfer data from all offices to the reporting instance in the main office.

  You also need to ensure that the solution has minimum effect on the performance of all trading servers. What should you do?

  A. Create a warm standby server in the main office by using log shipping from the branch offices.
  B. use distributed transactions in the branch offices to write the trade information to the local trading systems and the main office trading system.
  C. Create a mirrored database solution in high-safety mode in each branch office. Place the mirrored databases in the main office.
  D. Create a publication by using transactional replication in each branch office. Create subscriptions in the main office.
  D. Create a publication by using transactional replication in each branch office. Create subscriptions in the main office.

• Question 92:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain three tables. The structure of the three tables is as shown in the following table.

  

  You need to minimize disk space usage without altering the data types in the tables of the database. What should you do?

  A. Implement row-level compression on all tables.
  B. Implement row-level compression on Table1 and page-level compression on Table2 and Table3.
  C. Implement row-level compression on Table2 and page-level compression on Table1 and Table3.
  D. Implement row-level compression on Table3 and page-level compression on Table1 and Table2.
  B. Implement row-level compression on Table1 and page-level compression on Table2 and Table3.

• Question 93:

  You plan to deploy a new application.

  The application will perform the following operations:

  You need to configure a login to support the deployment of the new application. The solution must ensure that the application uses the most restrictive permissions possible.

  What should you do?

  A. Add the login to the sysadmin server role.
  B. Add the login to the dbcreator and securityadmin server roles.
  C. Add the login to the diskadmin and securityadmin server roles. Once the database is created, add a user to the db_backupoperator database role.
  D. Add the login to the diskadmin and serveradmin server roles. Once the database is created, add a user to the db_backupoperator database role.
  B. Add the login to the dbcreator and securityadmin server roles.

• Question 94:

  You are designing a table to store customer data. The table will contain a column to store the e-mail addresses of the customers.

  You need to recommend a solution to store the e-mail addresses.

  The solution must meet the following requirements:

  What should you recommend?

  A. Create an e-mail profile for the Database Mail SQL Server component.
  B. Use a CHECK constraint that uses a CLR function.C Add a custom schema to the database.
  C. Use a non-persisted computed column.
  B. Use a CHECK constraint that uses a CLR function.C Add a custom schema to the database.

• Question 95:

  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.[nnn] format (such as 12:55:30.123).

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

  Which data type should you use?

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

• Question 96:

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

  A database contains two tables named Orders and OrderDetails. There is also a data warehouse containing a table named factSales. The factSales table has a de-normalized structure and contains columns from Orders and OrderDetails.

  You plan to design a solution that will extract all data modifications from Orders and OrderDetails and load them into factSales.

  You have the following requirements:

  You need to implement the solution by using the least amount of coding and administrative effort.

  What should you do?

  A. Use the SQL Server Change Data Capture feature.
  B. Partition the Orders and OrderDetails tables based on date.
  C. Use the SQL Server Change Tracking feature.
  D. Use Microsoft Sync Services.
  A. Use the SQL Server Change Data Capture feature.

• Question 97:

  You have a table that has five varchar columns.

  You are designing an application that requires data in well-formed XML. You need to design a query statement that will produce the data in well-formed XML.

  What should you use?

  A. FOR XML PATH
  B. sp_xml_preparedocument
  C. XPATH query
  D. XSD schema
  A. FOR XML PATH

• Question 98:

  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?

  A. CROSS APPLY
  B. OUTER APPLY
  C. INNER JOIN
  D. LEFT OUTER JOIN
  A. CROSS APPLY

• Question 99:

  You are a database developer. You plan to create a database by using SQL Server 2008. The database will store information about students, teachers, classes, and rooms in a school. The database will be used by a scheduling application.

  In the design plan, the following facts have to be considered:

  You identify the following entities for the database design:

  You need to design the database to ensure normalization.What should you do?

  A. 1. Add a new entity named TeachersStudents.2. Establish a relationship between the Teachers and Students entities by using the TeachersStudents entity.
  B. 1. Add a new entity named ClassesRooms. 2. Establish a relationship between the Classes and Rooms entities by using the ClassesRooms entity.
  C. 1. Add a new entity named TeachersRooms. 2. Establish a relationship between the Teachers and Rooms entities by using the TeachersRooms entity
  D. 1. Create a new entity named StudentsRooms. 2. Establish a relationship between the Students and Rooms entities by using the StudentsRooms entity.
  B. 1. Add a new entity named ClassesRooms. 2. Establish a relationship between the Classes and Rooms entities by using the ClassesRooms entity.

• Question 100:

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

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