MICROSOFTOFFICIAL: New Updated 70-451 Exam Questions from Braindump2go 70-451 pdf dumps and 70-451 vce dumps! Welcome to download the newest Braindump2go 70-451 vce&pdf dumps: http://www.braindump2go.com/70-451.html (147 Q&As)
2015 Latest 70-451 Real exam questions to master and practice upon! Braindump2go Offers the New Updated Microsoft 70-451 147 Exam Questions in PDF & VCE files that can also be downloaded on every mobile device for preparation!
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
QUESTION 121
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?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
QUESTION 122
You are a database developer.
You plan to design a database solution by using SQL Server 2008.
A database contains a table named Customer.
The structure of the Customer table is as shown in the following table.
The solution uses the following stored procedure to return customer details.
Users report that the stored procedure takes a long time to execute.
You need to optimize the performance of the stored procedure.
What should you do?
A. Recreate the primary key as a nonclustered unique index and build a clustered index on the
country_id column.
B. Build a nonclustered index on the country_id column and use the INCLUDE clause to include
the customer_name column.
C. Build a nonclustered index on the country_id, customer_name, and customer_id columns.
D. Build a nonclustered index only on the country_id column.
Answer: B
QUESTION 123
You are a database developer.
You plan to design a database solution by using SQL Server 2008.
A frequently used query takes very long to execute.
You discover that the query frequently uses full- table scans instead of indexes.
This causes other queries that modify the table to be blocked.
The indexing strategy on the underlying tables that the query uses can change.
You need to design a solution that performs the following tasks:
– Removes full-table scans
– Allows the query optimizer to select the appropriate index.
What should you do?
A. Use the INDEX table hint.
B. Use the INDEX(0) table hint.
C. Use the NOEXPAND table hint.
D. Use the FORCESEEK table hint.
Answer: D
QUESTION 124
You are a database developer.
You develop a database by using SQL Server 2008 in an enterprise environment.
The database has a table named Sales.Inventory.
The table is partitioned into four geographic regions.
You update the Sales.Inventory table for each region by using the following stored procedure.
CREATE STORED PROCEDURE usp_Update
@RegionID tinyint
AS
UPDATE Sales.Inventory
SET Qty = T.CurrentQuantity
FROM Sales.Inventory I
JOIN Sales.TempData T ON I.ItemID = T.ItemID
AND I.RegionID = @RegionID;
The UPDATE statement locks the Sales.Inventory table when a single region is updated.
You need to prevent the locking of the Sales.Inventory table when a single region is updated. What should you do?
A. Modify the usp_Update stored procedure to use the NOLOCK table hint for the UPDATE
statement.
B. Modify the usp_Update stored procedure to use the SERIALIZABLE table hint for the
UPDATE statement.
C. Run the following Transact-SQL statement.
ALTER TABLE Sales.Inventory SET LOCK_ESCALATION = AUTO
D. Run the following Transact-SQL statement.
ALTER TABLE Sales.Inventory SET LOCK_ESCALATION = TABLE
Answer: C
QUESTION 125
You are a database developer.
You plan to design a database solution by using SQL Server 2008.
A stored procedure in a database uses a transaction to retrieve data from a table and produces aggregations.
You must design a solution that meets the following requirements:
– Update operations cannot be performed on the retrieved data while the stored procedure is being executed.
– Insert operations in the table can be performed while the stored procedure is being executed.
You need to ensure that the solution meets the requirements.
What isolation level should you use?
A. SERIALIZABLE
B. READ COMMITTED
C. REPEATABLE READ
D. READ UNCOMMITTED
Answer: C
QUESTION 126
You have a database that contains two tables.
Both the XACT_ABORT database option and the IMPLICIT_TRANSACTIONS database option are set to OFF.
You need to update both tables.
If an update fails on either table, neither table should be updated.
What should you do?
A. Use a transaction.
B. Change the isolation level.
C. Use the TABLOCK query hint.
D. Use the UPDLOCK query hint.
Answer: A
QUESTION 127
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:
– The database stores various types of reports from multiple sources.
– The solution allows search by keywords.
– The database stores large amounts of data.
– The database is scalable.
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.
Answer: D
QUESTION 128
You are a database developer.
You plan to design a database solution by using SQL Server 2008.
The database contains a table that has two partitions.
The first partition contains the current data, and the second partition contains historical data.
There is a high frequency of data manipulation on the current data.
The testers report that they are frequently unsuccessful in querying the historical data only.
You discover that locks are being escalated to the table-level lock.
You need to ensure that without modifying the SELECT statements, you enable users to query the historical data.
You want to achieve this goal by using the minimum amount of administrative effort and minimally affecting other queries.
What should you do?
A. Use the ALTER TABLE … SET (LOCK_ESCALATION = AUTO) command.
B. Move the historical partition to a read-only filegroup.
C. Set the Server Startup Parameters to include -T1211.
D. Set DEADLOCK PRIORITY to HIGH.
Answer: D
QUESTION 129
You are designing a new database.
The tables of the database will be replicated to multiple offices.
You need to design the tables to meet the following requirements:
– A row identifier must be set when a row is created
– The row identifier must be unique across the entire organization
What should you recommend?
A. Add a column to all the tables that have the hierarchyid data type.
B. Add a column to all the tables that have a time data type.
Configure the DEFAULT constraint to use the GETDATE () function.
C. Add a column to all the tables that have the identity property.
D. Add a column to all the tables that have a uniqueidentifier data type.
Configure the DEFAULT constraint to use the NEWIDQ function.
Answer: D
QUESTION 130
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
Answer: A
Braindump2go Regular Updates of Microsoft 70-451 Preparation Materials Exam Dumps, with Accurate Answers, Keeps the Members One Step Ahead in the Real 70-451 Exam. Field Experts with more than 10 Years Experience in Certification Field work with us.
70-451 PDF Dumps & 70-451 VCE Dumps Full Version Download(147q): http://www.braindump2go.com/70-451.html