Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 1:

  You are designing a document repository application that will contain over 100,000 documents. The repository will have the following specifications: What should you include in the design?

  A. an XML data type
  B. nvarchar() null
  C. sparse columns
  D. varchar(max) not null
  C. sparse columns

• Question 2:

  You have a server that runs SQL Server 2008 Enterprise. The server contains a 300-GB database.

  At the end of every month, several reports are generated from the database.

  Users report that it takes a long time to access data from the database while the reports are being generated.

  You need to recommend a solution to improve the data access time while the reports are being generated from the database. The solution must use a minimum amount of administrative effort.

  What should you recommend?

  A. linked servers
  B. database snapshots
  C. a failover cluster
  D. database mirroring
  D. database mirroring

• Question 3:

  You are developing a security policy for the SQL Server developers in your organization.

  You need to create a security strategy to protect Transact-SQL applications from SQL injection attacks.

  What should you include in the strategy?

  A. Require certificates for Service Broker communications.
  B. Parse input parameters to prevent the use of the following strings: ;--/**/
  C. Parse input parameters to prevent the use of the following strings:and@//++
  D. Disable Service Broker.
  B. Parse input parameters to prevent the use of the following strings: ;--/**/

• Question 4:

  You are designing a CLR stored procedure that will access a remote Web service.

  You need to ensure that the CLR stored procedure can access the remote Web service.

  Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

  A. Grant EXECUTE on the CLR stored procedure.
  B. Implement a custom trust level policy.
  C. Configure the CLR stored procedure to use the SAFE permission set.
  D. Configure the CLR stored procedure to use the EXTERNAL_ACCESS permission set.
  B. Implement a custom trust level policy.
  D. Configure the CLR stored procedure to use the EXTERNAL_ACCESS permission set.

• Question 5:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a table named Policies. The table contains information about 100 million insurance policies. A complex stored procedure

  executes daily to calculate the risk amount of each policy and stores the information in the table.

  When the stored procedure is executed, users experience poor performance and query time-out errors.

  The queries used in the stored procedure are optimized for performance. You need to ensure that the disruption to users is minimal while the stored procedure is being executed.

  What should you do?

  A. Use the READ UNCOMMITTED transaction isolation level.
  B. Split the execution of the stored procedure into batches.
  C. Write the risk amounts to a table variable before you update the Policies table.
  D. Write the risk amounts to a temporary table before you update the Policies table.
  B. Split the execution of the stored procedure into batches.

• Question 6:

  You use SQL Server 2008 to design a database that will hold incoming XML responses for an EDI system.

  You have the following requirements:

  The data is accessible to heterogeneous platforms:

  You need to design the database to meet the given requirements.

  What should you do?

  A. Use SQL Server 2008 tables to store data and include proper indexes.
  B. Use ANSI text files to store text reports, and use SQL Server 2008 tables to store numerical reports.
  C. Save reports in binary format in a file within a Windows folder. Save the path of the file in SQL Server 2008 tables.
  D. Store reports in XML format, and use SQL Server 2008 tables to store the data. Index the XML data to improve performance.
  D. Store reports in XML format, and use SQL Server 2008 tables to store the data. Index the XML data to improve performance.

• Question 7:

  You have production tables that are based on data from staging tables.

  You plan to create stored procedures that will perform the following actions on the production tables:

  You need to develop a strategy for the planned stored procedures to minimize locking operations. The solution must be developed by using use the minimum amount of effort.

  What should you use?

  A. Transactions that have the READ UNCOMMITTED isolation level
  B. Transactions that have SERIALIZABLE isolation level
  C. a MERGE statement that uses a READCOMMITTEDLOCK table hint
  D. a MERGE statement that uses the OUTPUT clause
  D. a MERGE statement that uses the OUTPUT clause

• Question 8:

  You need to design a table that will contain a monetary value. The value must support five digits to the right of the decimal point. Which data type should you choose?

  A. decimal
  B. bigint
  C. int
  D. money
  A. decimal

• Question 9:

  You have a table named Sales that lists all of the products sold by your company.

  The Sales table contains a column named UnitsSold. UnitsSold contains the total number of units sold of each product. Each product appears in only one row.

  You have the following view:

  

  You discover that the view does not return a list of the top 100 selling products. You need to ensure that the view returns a list of the top 100 selling products. Which SQL statement should you use?

  

  B. C. D.
  C

• Question 10:

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

  The database contains two tables named Supplier and Product. There is a foreign key constraint between the Supplier and Product tables on the SupplierID column. The Supplier table contains a row that has the SupplierID value as 0. The 0

  value indicates that the supplier is deleted. Certain transactions delete the supplier records from the Supplier table. You need to ensure that if a supplier is deleted, the SupplierID value in the Product table is set to 0.

  What should you do?

  A. Create a FOR DELETE trigger on the Product table that updates the SupplierID value to 0 in the Products table for the deleted supplier.
  B. Create a FOR DELETE trigger on the Supplier table that updates the SupplierID value to 0 in the Products table for the deleted supplier.
  C. Create a default constraint on the SupplierID column in the Product table that sets the value to 0. Set the ON DELETE property of the foreign key constraint to NULL.
  D. Create a default constraint on the SupplierID column in the Product table that sets the value to 0. Set the ON DELETE property of the foreign key constraint to Default.
  D. Create a default constraint on the SupplierID column in the Product table that sets the value to 0. Set the ON DELETE property of the foreign key constraint to Default.