Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 61:

  You have a table that contains an XML column named XMLDatal. The column contains the following nodes:

  You need to design a stored procedure that will return only the title and description in a tabular result set.

  The solution must be developed by using minimum amount of effort.

  How should you extract the information?

  A. Execute sp_preparedocument.
  B. Use FOR XML PATH in a select statement.
  C. Use XMLDatal.Exists () in a select statement.
  D. Use XMLDatal.Query () in a select statement.
  D. Use XMLDatal.Query () in a select statement.

• Question 62:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. You configure a database on a server to use a common language runtime (CLR). You need to create a CLR assembly that enables the CLR stored procedure to access environment variables available on the server. You also need to ensure that the CLR assembly has the minimum permissions assigned.

  What should you do?

  A. Enable the TRUSTWORTHY database property.
  B. Create the assembly by using the SAFE permission set.
  C. Create the assembly by using the UNSAFE permission set.
  D. Create the assembly by using the EXTERNAL ACCESS permission set.
  D. Create the assembly by using the EXTERNAL ACCESS permission set.

• Question 63:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. The database supports a warehousing application and contains data from two warehouses in a table named Product. The Product table

  contains a warehouse indicator field named warehouse_id.

  The two Warehouse B contains 55,000 items.

  The solution uses a third-party application that runs on SQL Server 2008. The application uses a stored procedure that returns the warehouse inventory based on the warehouse_id parameter. Users report that occasionally, the system

  performance is unacceptable when the Warehouse A inventory is queried. You cannot modify the stored procedures in the application. You need to ensure acceptable performance when users query the inventory of Warehouse A.

  What should you do?

  A. Create a clustered index on the warehouse_id column.
  B. Create a nonclustered index on the warehouse_id column.
  C. Create a plan guide that sets the MAXDOP query hint to 1.
  D. Create a plan guide that uses the OPTIMIZE FOR clause for Warehouse A.
  D. Create a plan guide that uses the OPTIMIZE FOR clause for Warehouse A.

• Question 64:

  You have a table that contains 5 million rows. The table has a clustered index. You plan to add an additional index on a column that contains 80 percent null values. The column is used extensively in WHERE clauses.

  You need to implement an index strategy for the column. The solution must minimize the amount of storage space required for the index.

  Which type of index should you use?

  A. clustered
  B. filtered
  C. nonclustered
  D. unique
  B. filtered

• Question 65:

  A new data analytics application is being implemented in your organization.

  Users will run a process that compares result sets before and after executing complex data modifications.

  The data will always be queried by using the same conditions.

  Some tables updated by the processes will not need to be compared.

  Your need to design a locking strategy for the process that meets the following requirements:

  What should the strategy include?

  A. Set the transaction isolation level to SERIALIZABLE.
  B. Use a transaction that uses the WITH (NOLOCK) hint.
  C. Use a transaction that uses the WITH (HOLDLOCK) hint.
  D. Set the transaction isolation level to READ UNCOMMITTED.
  B. Use a transaction that uses the WITH (NOLOCK) hint.

• Question 66:

  You have a database that contains two tables named Table1 and Table1_Details. Table1_Details contains details about 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 Null.
  B. Create a foreign key relationship. Set Cascade Delete to True.
  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_Details.
  B. Create a foreign key relationship. Set Cascade Delete to True.

• Question 67:

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

  The database contains a large table that has 20 million rows. The table contains the following columns:

  The table currently has single-column nonclustered indexes on the CustomerNumber, CompanyName, and ContactFirstName columns.

  An application uses data from this table. The user interface of the application allows the usage of any one filter from the following list of filters:

  In all cases, the listed order of the columns is the order in which they will appear in the WHERE clause that is generated.

  You need to design an indexing strategy for this table, so that the query optimizer can quickly perform an index seek when searching through the table data.

  What should you recommend?

  A. Drop all existing indexes. Create two multicolumn indexes, one on CustomerNumber and CompanyName and the other on ContactLastName and ContactFirstName.
  B. Drop all existing indexes. Create two multicolumn indexes, one on CompanyName andCustomerNumber and the other on ContactLastName and ContactFirstName.
  C. Drop the indexes on CustomerNumber and ContactFirstName. Create two multicolumn indexes, one on CustomerNumber and CompanyName and the other on ContactLastName and ContactFirstName.
  D. Drop all existing indexes. Create a new index on ContactLastName and ContactFirstName. Create a multicolumn index on CustomerNumber and CompanyName.
  C. Drop the indexes on CustomerNumber and ContactFirstName. Create two multicolumn indexes, one on CustomerNumber and CompanyName and the other on ContactLastName and ContactFirstName.

• Question 68:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. You create a table that contains information about Web pages that are added to a Web site. The Web site has a home page and contains

  various other Web pages. The home page is the root page of the site. All pages except the root page have a link to an upper-level page.

  The table must support the following design considerations:

  What should you use?

  A. Use the XML data type.
  B. Use the hierarchyid data type.
  C. Use a Parent/Child mechanism that references the same table.
  D. Use a Parent/Child mechanism that references one or more additional tables.
  B. Use the hierarchyid data type.

• Question 69:

  You have a table named Sales that contains the data listed in the following table.

  

  You need design a query that retrieves a list that contains all sales people that have top three values from the TotalSales column. Which query should you design?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

• Question 70:

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

  The database application has a table named Transactions that contains millions of rows. The table has multiple columns that include transaction_id and transaction_date. There is a clustered index on the transaction_id column. There is a

  nonclustered index on the transaction_date column.

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

  

  You need to ensure that the query retrieves data in minimum possible time. What should you do?

  A. Create a nonclustered index on the transaction_type_id column.
  B. Create a nonclustered index on the transaction_date and transaction_type_id columns.
  C. Create a nonclustered index on the transaction_date column and include the transaction_type_id andtransaction_notes columns.
  D. Create a nonclustered index on the transaction_date and transaction_type_id columns and include the transaction_notes column.
  D. Create a nonclustered index on the transaction_date and transaction_type_id columns and include the transaction_notes column.