Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 121:

  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:

  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:

  What should you do?

  A. Create an indexed view from the SalesDetails table that contains aggregated data of all the columns required by all the reports.
  B. Create multiple indexed views from the SalesDetails table so that each view contains aggregated data of only the columns required by the respective report.
  C. Create multiple Report tables from the SalesDetails table so that each Report table contains aggregated data of only the columns required by the respective report. Create views on top of each of the Report tables.
  D. Perform a quick transfer of aggregated new records to a staging table at the end of each month.Create an indexed view from the staging table that contains aggregated data of all the columns required by all the reports.
  B. Create multiple indexed views from the SalesDetails table so that each view contains aggregated data of only the columns required by the respective report.

• Question 122:

  You are a database developer for a retail application. You create a database by using SQL Server 2008 in a distributed enterprise environment that has multiple servers. The same database is implemented on all the servers. The database

  contains a table that has a surrogate key.

  You need to ensure that the following requirements are met:

  What should you do?

  A. Use the timestamp data type.
  B. Use the bigint data type. Use the IDENTITY property in the column definition.
  C. Use the uniqueidentifier data type. Use the NEWID() function in a default constraint.
  D. Use the uniqueidentifier data type. Use the NEWSEQUENTIALID() function in a default constraint.
  D. Use the uniqueidentifier data type. Use the NEWSEQUENTIALID() function in a default constraint.

• Question 123:

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

  A database contains a large non-partitioned table with 4 million rows. You write a stored procedure that updates approximately 4,000 rows in the table.

  The stored procedure is scheduled to run only during peak hours. The stored procedure runs frequently.

  Users report performance issues. You discover that when the stored procedure runs, it results in memory contention because of a high number of locks placed on the table.

  You need to ensure that the SQL Server 2008 server places the minimum possible number of locks on the table while the stored procedure is being executed.

  What should you do?

  A. set the LOCK_ESCALATION option of the table to AUTO.
  B. Add the PAGLOCK hint to the UPDATE statement in the procedure.
  C. Disable the LOCK_ESCALATION option of the table.
  D. Add the TABLOCK hint to the UPDATE statement in the procedure.
  D. Add the TABLOCK hint to the UPDATE statement in the procedure.

• Question 124:

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

  There are two schemas named Sales and Marketing. You are the owner of the Sales schema and the Marketing schema is owned by a user named MarketingUser.

  Users of the Marketing schema do not have permissions to access the Sales schema. You have permissions to create objects in all schemas in the database.

  The Sales schema has a table named Customers.

  You plan to create a stored procedure in the Marketing schema for the marketing team. The stored procedure will select data from the Customers table and will be owned by MarketingUser.

  You need to ensure that the marketing team is able to execute the stored procedure.

  What should you do?

  A. Create the procedure by using the EXECUTE AS SELF option.
  B. Create the procedure by using the EXECUTE AS CALLER option.
  C. Create the procedure by using the EXECUTE AS OWNER option.
  D. Create the procedure by using the EXECUTE AS USER=MarketingUser option.
  A. Create the procedure by using the EXECUTE AS SELF option.

• Question 125:

  You are a database developer. You plan to create a database by using SQL Server 2008. The database has a table named Employees. The table contains records of employees and their managers.

  The table includes the EmployeelD and ManagerlD fields. The EmployeelD values are unique. The value in the ManagerlD field is the employee ID of the employee's manager.

  A Web site requires XML formatted output of all managers and employees to be displayed as a tree diagram.

  You need to produce the required output by querying the database without using system stored procedures.

  What should you do?

  A. Create a table-valued function by using the hierarchyid data type.
  B. Create a scalar-valued function by using the FOR XML PATH clause and the TYPE directive.
  C. Create a scalar-valued function by using the OPENXML () function.
  D. Create a table-valued function by using a common table expression (CTE).
  B. Create a scalar-valued function by using the FOR XML PATH clause and the TYPE directive.

• Question 126:

  You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment.

  You are creating a SQL Agent job that uses Transact-SQL to update data in two related databases on two different servers.

  You have the following requirements:

  What should you do?

  A. Ensure both databases are altered using the NO_WAIT termination clause.
  B. Use the Windows Sync Manager to ensure that the databases can never be out of synchronization.
  C. Use saved transactions. When a database failure occurs, restore both databases by using a saved transaction.
  D. Use marked transactions. When a database failure occurs, restore both databases by using a marked transaction.
  D. Use marked transactions. When a database failure occurs, restore both databases by using a marked transaction.

• Question 127:

  You have a table named Books that contains information about books. Books has the columns in the following table.

  

  You plan to create several queries that will filter on Title and ISBN. The queries will return values from Title, ISBN, and Description.

  You need to recommend an indexing solution to meet the following requirements:

  What should you recommend?

  A. Create a nonclustered index on each column.
  B. Create a clustered index on Title, ISBN and Description as the key value.
  C. Create a clustered index on Title and ISBN and set the index fill factor to 75.
  D. Create a nonclustered index on Title and ISBN and include the Description column.
  D. Create a nonclustered index on Title and ISBN and include the Description column.

• Question 128:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. You are creating a database to support the office manager. Your database model has the following structure.

  

  The database design has the following business requirements:

  When an employee is no longer available to complete a task, the employee link to the assignment is replaced with a NULL value.

  You need to implement the business requirements to maintain data integrity. What should you do?

  A. Create DDL INSERT triggers on the Employee, Task, and Assignment entities.
  B. Create CHECK constraints on the TaskID and EmployeeID attributes in the Assignment entity.
  C. Create Foreign Keys constraints on the TaskID and EmployeeID attributes in the Assignment entity.
  D. Create Foreign Keys constraints on the TaskID and EmployeeID attributes in the Task and Employee entities respectively. Reference the Assignment entity, and specify the appropriate On Delete action.
  C. Create Foreign Keys constraints on the TaskID and EmployeeID attributes in the Assignment entity.

• Question 129:

  You have a server that runs SQL Server 2008. The server supports an online application.

  New products are constantly being added to a database stored on the server.

  You identify the following:

  You need to recommend a search process that meets the following requirements:

  What should you do?

  A. Create a stoplist that contains the product descriptions.
  B. Create a nonclustered index and include the product description.
  C. Create filtered indexes on the product description.
  D. Implement Full-Text Search.
  D. Implement Full-Text Search.

• Question 130:

  You have a server that runs SQL Server 2008 Enterprise.

  You have a query that contains SUM, AVG, and MAX functions.

  You discover that the query takes a long time to execute.

  You need to reduce the amount of time required to execute the query.

  What should you do?

  A. Add computed columns that have persisted values to the table that contains the aggregate values on a per row basis.
  B. Create an indexed view in which each aggregate function is computed.
  C. Add computed columns to the table that contains the aggregate values on a per row basis.
  D. Create a view in which each aggregate function is computed.
  B. Create an indexed view in which each aggregate function is computed.