Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 101:

  You are designing a database that will be used for reporting purposes. The database stores data using spatial data type.

  You need to minimize the data storage requirements and improve the application response time.

  What should you recommend?

  A. Use XML datatype.
  B. Use table partitioning.
  C. Use row compression.
  D. Use sparse columns.
  C. Use row compression.

• Question 102:

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

  The database will store multilingual data.

  The database will contain a table that has 100 million rows.

  The table will contain 1,000 columns that are based on the nvarchar(max) data type. For each column, only 2 percent of the rows will be populated.

  You need to design the table to optimize storage space.

  What should you do?

  A. Use row compression.
  B. Use NTFS file system compression to reduce the disk space used.
  C. Define the columns as sparse columns.
  D. Change the column data types to varchar(max).
  C. Define the columns as sparse columns.

• Question 103:

  You have a stored procedure that uses a cursor. The stored procedure updates several related tables.

  You discover that the stored procedure runs slowly and uses a significant amount of resources on the database server.

  You need to recommend changes to the stored procedure to meet the following requirements:

  What should you recommend?

  A. Change the cursor to a dynamic cursor.
  B. Change the cursor to a client-side cursor.
  C. Rewrite the cursor by using set-based operations.
  D. Rewrite the cursor by using recursive CLR stored procedure.
  C. Rewrite the cursor by using set-based operations.

• Question 104:

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

  The database will contain a table named Claims. The Claims table will contain a large amount of data.

  You plan to partition the data into following categories:

  The close_date field in the Claims table is a date data type and is populated only if the claim has been closed.

  You need to design a partition function to segregate records into the defined categories.

  What should you do?

  A Create a RANGE RIGHT partition function by using the values 20051231, 20071231, and NULL.

  A. Create a RANGE LEFT partition function by using the values 20051231, 20071231, and 20080101.
  B. Create a RANGE LEFT partition function by using the values 20051231, 20071231, and NULL. D Create a RANGE RIGHT partition function by using the values 20051231, 20071231, and 20080101.
  C. Open claims
  D. Claims closed before January 1, 2005
  E. Claims closed between January 1, 2005 and December 31, 2007
  F. Claims closed from January 1, 2008 till date
  A. Create a RANGE LEFT partition function by using the values 20051231, 20071231, and 20080101.

• Question 105:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. Users report poor performance of a long-running query in a database.

  You fail to detect the cause of poor performance when you examine the estimated execution plan. On viewing the actual execution plan, you discover that several tasks are CPU-intensive.

  You need to ensure that you can view an estimated execution plan containing the most likely actions.

  You want to achieve this goal by incurring minimum possible CPU overhead.

  What should you do?

  A. Rebuild all indexes.
  B. Use the SET STATISTICS PROFILE ON statement.
  C. Use the SET STATISTICS XML ON statement.
  D. Update statistics on the appropriate tables.
  D. Update statistics on the appropriate tables.

• Question 106:

  You are designing a database table for a content management system. Users will store images and videos in the database.You need to ensure that the database can store files that are 20 MB or less.

  The solution must minimize the amount of space required to store the data.

  Which data type should you use?

  A. binary(20)
  B. varbinary(20)
  C. varbinary(max)
  D. XML
  C. varbinary(max)

• Question 107:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a table that has a column defined as a smallint data type. The table is partitioned on has boundaries of 100 and 1,000.

  The table must be altered to contain the following partitions: < 100 >= 100 and < 400 >= 400 and < 700 >= 700 and < 1000 >= 1000

  You need to alter the partition function to provide the required partitions.

  Which code fragment should you use?

  A. ALTER PARTITION FUNCTION MyRangePF1 () SPLIT RANGE (399); GO ALTER PARTITIONFUNCTION MyRangePF1 () SPLIT RANGE (699); GO
  B. ALTER PARTITION FUNCTION MyRangePF1 () SPLIT RANGE (400); GO ALTER PARTITIONFUNCTION MyRangePF1 () SPLIT RANGE (700); GO
  C. DROP PARTITION FUNCTION myRangePF1; GOCREATE PARTITION FUNCTION myRangePF1 (smallint)AS RANGE RIGHT FOR VALUES (99, 399, 699, 999);
  D. DROP PARTITION FUNCTION myRangePF1; GOCREATE PARTITION FUNCTION myRangePF1 (smallint)AS RANGE LEFT FOR VALUES (100, 400, 700, 1000);
  B. ALTER PARTITION FUNCTION MyRangePF1 () SPLIT RANGE (400); GO ALTER PARTITIONFUNCTION MyRangePF1 () SPLIT RANGE (700); GO

• Question 108:

  Your company has four offices. Each office contains a server that runs SQL Server 2008.

  All corporate data is stored in a central location.

  You need to create a reporting solution. The solution must meet the following requirements:

  What should you do?

  A. Implement database mirroring.
  B. Implement log shipping.
  C. Create horizontally partitioned tables and a federated database.
  D. Implement a geographically dispersed SQL Server 2008 cluster.
  B. Implement log shipping.

• Question 109:

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

  You have the following requirements:

  You need to design the database to meet these requirements.

  What should you do?

  A. Use SQL Server 2008 tables to store the data in a varchar (max) column.
  B. Use SQL Server 2008 tables to store the data in a varbinary (max) column with FILESTREAM option.
  C. Use SQL Server 2008 tables to store the data in a varbinar y(max) column.
  D. Use SQL Server 2008 tables to store the data using XML datatype.
  D. Use SQL Server 2008 tables to store the data using XML datatype.

• Question 110:

  You need to provide a developer the ability to create and modify database diagrams from an existing database by using SQL Server Management Studio. The solution must minimize the amount of permissions assigned to the developer. What should you do?

  A. Add the developer to the sysadmin role.
  B. Add the developer to the db_owner role.
  C. Grant the developer the CREATE TABLE permission only.
  D. Grant the developer the CREATE SCHEMA permission only.
  B. Add the developer to the db_owner role.