Are You Interested in Successfully Completing the Microsoft 70-451 Certification Then Start to Earning Salary? Braindump2go has Leading Edge Developed Microsoft Exam Questions that will Ensure You Pass this 70-451 Certification! Braindump2go Delivers you the Most Accurate, Current and Latest Updated 70-451 Certification Exam Questions Availabe with a 100% Money Back Guarantee Promise!
Vendor: Microsoft
Exam Code: 70-451
Exam Name: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
Keywords: 70-451 Exam Dumps,70-451 Practice Tests,70-451 Practice Exams,70-451 Exam Questions,70-451 PDF,70-451 VCE Free,70-451 Book,70-451 E-Book,70-451 Study Guide,70-451 Braindump,70-451 Prep Guide
70-451 PDF Dumps & 70-451 VCE Dumps (147q) Full Version Download: http://www.braindump2go.com/70-451.html
QUESTION 51
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
Answer: B
QUESTION 52
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:
– Minimize the amount of time required to return the results of the planned queries
– Minimize the number of Indexes
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.
Answer: D
QUESTION 53
You are a database developer.
You plan to design a database solution by using SQL Server 2008.
The database supports a Web site and captures user interactions.
These interactions are stored in the Activity table of the User_Activity database.
Data older than six months is archived to the Activity table of the Archive_Activity database on a different instance of SQL Server 2008.
The structure of the Activity table is as shown in the following table.
You plan to design a solution that allows a single query to generate a report that summarizes user interactions for the last 12 months.
You need to ensure that the solution is implemented.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. Create a partition function and a partition scheme.
B. Modify the Activity tables to use the partition scheme.
C. Move the archived data back to the User_Activity database.
D. Create a view by using the UNION ALL clause to retrieve data from the two Activity tables.
E. Create CHECK constraints on the two Activity tables to limit the values in the activity_date
column to an exclusive range.
Answer: DE
QUESTION 54
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.
Answer: D
QUESTION 55
You are a database developer.
You plan to design a database solution by using SQL Server 2008.
You plan to design a complex multi-statement stored procedure in the following manner.
CREATE PROCEDURE Sales.GetCustomerActivity
@StartDate datetime
AS
SELECT order_id, order_date, customer_id
FROM Sales.Orders
WHERE order_date >= @StartDate
…
On testing, you discover that the stored procedure occasionally takes a longer than expected time to execute.
You discover that this degradation is caused by the first statement in the stored procedure.
You need to ensure that the stored procedure is consistently executed in the minimum possible time.
What should you do?
A. Run the EXEC sp_recompile GetCustomerActivity command.
B. Create a plan guide to apply the OPTION (RECOMPILE) clause to the first statement.
C. Modify the stored procedure by adding the WITH RECOMPILE clause.
D. Replace the first statement in the stored procedure with the following Transact-SQL statement.
UPDATE STATISTICS Sales.GetCustomerActivity WITH RESAMPLE
Answer: B
QUESTION 56
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.
Answer: B
QUESTION 57
You are a database developer.
You plan to design a database solution by using SQL Server 2008.
The database contains a table that is stored as a heap.
You perform page-level compression on the table.
When data is added to the table by using BULK INSERT, the new pages are not compressed.
You need to ensure page-level compression for all data pages when they are allocated to the table.
Which query hint should you use?
A. PAG LOCK
B. NOLOCK
C. NOWAIT
D. TABLOCK
Answer: C
QUESTION 58
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.
Answer: D
QUESTION 59
Your company is developing an application.
The database platform for the application will be vendor-independent.
You need to ensure that the application automatically stores the current date and time data when a record is inserted or updated, regardless of the database platform.
What should you do?
A. Use Entity Framework and DateTime.Now.
B. Create a database column that has a default value of GETDATEQ.
C. Create an INSERT trigger that uses the GETDATEQ function.
D. Use the datetime2 data type.
Answer: A
QUESTION 60
A database contains two tables named Table1 and Table1_Details.
Table1_Details contains details about the 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 Set Null.
B. Create a foreign key relationship. Set Cascade Delete to Cascade.
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_Detai!s.
Answer: B
Braindump2go New Released 70-451 Dump PDF Free Download, 147 Questions in all, Passing Your Exam 100% Easily!
http://www.braindump2go.com/70-451.html (147Q&As)