70-451 Royal Pack Testengine pdf
100% Actual & Verified — 100% PASS
Unlimited access to the world's largest Dumps library! Try it Free Today!Get it now →
Exam Number/Code: 70-451
Exam name: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing
Free of 70-451 latest exam materials and testing engine for Microsoft certification for examinee, Real Success Guaranteed with Updated 70-451 pdf dumps vce Materials. 100% PASS Today!
2016 Mar 70-451 Study Guide Questions:
Q55. You need to design a solution to guarantee that a stored procedure is part of every transaction.
A. Enable Implict transaction.
B. Set the NOCOUNT option to on.
C. Set the XACT_ABORT option to on.
D. Enable distributed transaction Cordinatoor (DTC).
Q56. You intend to import data from an external source into a table. You must make sure that the tasks below areachieved:
The rows that fail the foreign key constraints during import are inserted into a separate table.
Even if the import encounters rows that fail foreign key constraints, it is successfully completed.
So what should you do?
A. An AFTER trigger has to be used
B. CHECK constraints have to be used
C. An INSTEAD OF trigger has to be used
D. During the import process disable the foreign keys
Q57. You are designing a database that will store telephone numbers. You need to ensure that only phone numbersthat use a specific format are written to the database. What should you create?
A. a CHECK constraint
B. a computed column
C. a DEFAULT constraint
D. a persisted computed column
Q58. You are a database developer. You plan to design a database solution by using SQL Server 2008. A databasecontains a table named Person. The structure of the table is as shown in the following exhibit.
The table has the following indexes:
A unique clustered index on the PersonID column named IX_Person_PersonID
A nonclustered index on the FirstName and LastName columns named IX_Person_FirstName_LastName
A nonclustered index on the PersonType column named IX_Person_PersonType that has FirstName andLastName as included columns
The table contains approximately 700,000 records. The approximate number of records for each PersonTypeis 3,000. You execute the following query:
SELECT P.FirstName, P.LastName
FROM Person PWHERE P.
PersonType = 'DR'
You plan to analyze the performance of the query by using an execution plan. You need to ascertain that theindexes are used optimally. What should you do?
A. Verify that a clustered index scan operation is performed on the IX_Person_PersonID index.
B. Verify that an index seek operation is performed on the IX_Person_PersonType index.
C. Verify that an index seek operation is performed on the IX_Person_PersonType index, and a keylookupoperation is performed on the IX_Person_PersonID index.
D. Verify that an index seek operation is performed on the IX_Person_PersonType index, and an indexscanoperation is performed on the IX_Person_FirstName_LastName index.
Q59. You are designing a database that will be used for reporting purposes. You need to minimize the data storagerequirements and improve the application response time. What should you recommend?
A. row compression
B. sparse columns
C. table partitioning
Q60. You are a database developer. You develop solutions by using SQL Server 2008 in an enterpriseenvironment. You are creating a SQL Agent job that uses Transact-SQL to update data in two relateddatabases on two different servers. You have the following requirements:
The job can only execute once each evening.
The databases on each server use the full-recovery model.
Transaction log backups for the two databases occur at different times.
The job uses transactions to ensure that in the event of an error, all updates are rolled back.
You need to ensure that when you restore a database on either server, the two databases are restored to astate that reflects the last time the job successfully executed. What should you do?
A. Ensure both databases are altered using the NO_WAIT termination clause.
B. Use the Windows Sync Manager to ensure that the databases can never be out of synchronization.
C. Use saved transactions. When a database failure occurs, restore both databases by using a savedtransaction.
D. Use marked transactions. When a database failure occurs, restore both databases by using a markedtransaction.
Replace 70-451 sample question:
Q61. You are a database developer. You plan to design a database solution by using SQL Server 2008. A storedprocedure in a database uses a transaction to retrieve data from a table and produces aggregations. You mustdesign a solution that meets the following requirements:
Update operations cannot be performed on the retrieved data while the stored procedure is beingexecuted. 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?
B. READ COMMITTED
C. REPEATABLE READ
D. READ UNCOMMITTED
Q62. You are a database developer. You plan to design a database solution by using SQL Server 2008. Accountmanagers in your company store order data in a database. Your company requires a list of customers for eachaccount manager. The list must be sorted in the descending order of the order amount. You create a querythat generates the list at the end of each month. You need to ensure that the query executes as quickly aspossible. What should you do?
A. Create a cursor that returns each account manager, and then sort the order data by order amount.
B. Use a SELECT statement that uses the OVER clause to rank the customers by order amount foreachaccount manager.
C. Create a correlated subquery to return the order amount for each account manager.Sort the results first byaccount manager and then by order amount.
D. Create a table-valued function that returns the order amount for a specific account manager, andthencreate a query by using the CROSS APPLY clause to list each account manager.Sort the results firstby account manager and then by order amount.
Q63. You need to provide a developer the ability to create and modify database diagrams from an existingdatabase by using SQL Server Management Studio. The solution must minimize the amount of permissionsassigned 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.
Q64. You are a database developer. You provide solutions by using SQL Server 2008 in an enterprise environment.Your online transaction processing (OLTP) database contains a tabled named SalesOrders. Your datawarehouse contains a table named factBuyingHabits. The factBuyingHabits table has no indexes. You need tosynchronize data between the two tables on a weekly basis. The synchronization process has the followingrequirements:
New records in the SalesOrders table are inserted in the factBuyingHabits table.
When a record is modified in the SalesOrders table, the modification is updated in the factBuyingHabitstable.
Records that are deleted from the SalesOrders table are also deleted from the factBuyingHabits table.
You need to design an appropriate synchronization solution. You want to achieve this goal by using minimumamount of coding and administrative efforts. What should you do?
A. Design an SSIS package each for the INSERT, UPDATE and DELETE operations. Schedule a job to runthis package.
B. Design a single SSIS package that uses the Slowly Changing Dimension task. Schedule a job to run thispackage.
C. Write one stored procedure that contains a MERGE statement to perform the INSERT, UPDATE andDELETE operations. Schedule a job to run the stored procedure.
D. Write three stored procedures each for the INSERT, UPDATE and DELETE operations. Schedule a job torun the stored procedures in a sequential manner.
Q65. Data from two storerooms are contained in the database in a table named Manufacures. The two storeroomsare respectively called Storeroom1 and Storeroom2. And the database supports a warehousing application. Astoreroom indicator field named storeroom_id is contained in the Manufacture table. Storeroom1 includes275,000 items and Storeroom2 includes 55,000 items. A third-party application is used by the solutions. Andthe third-party application runs on SQL Server 2008 and uses a stored procedure that returns the storeroominventory based on the warehouse_id parameter. You notice that when users query the storeroom1 inventory, users sometimes experience poor system performance. But the stored procedures cannot be modified in theapplication. So when the inventory of Storeroom1 is quried, what should you do to ensure the systemperformance?
A. On the storeroom_id column, a clustered index should be created.
B. On the storeroom_id column, a non-clustered index should be created.
C. A plan guide should be created that the MAXDOP query hint is set to 1.
D. A plan guide that uses the OPTIMIZE FOR clause for Storeroom1 should be created.
Q66. You are a database developer. You plan to design a database solution by using SQL Server 2008. A databasecontains a table named Employee_Vacation. You are given an updated list of employee vacations used. Thelist is in an XML formatted file. The extract of the XML format is written in the following manner:
You plan to import the data and update the Employee_Vacation table. You design a query to calculate thenumber of vacation days used by each employee. You need to ensure that vacation days are accuratelycounted for each employee. What should you do?
A. Use an XQuery expression along with the LET clause and the count function. Return the count inXMLformat.
B. Use an XML index. Aggregate the number of vacation days for each employee, and then return thetotalcount in XML format.
C. Use the OPENXML function to convert XML data into a standard table format.Execute the Transact- SQLcount function on the vacation days, and then return the count in XML format.
D. Use an XQuery expression to write the information from XML format to a SQL Server table. Aggregate thenumber of vacation days from the tables, and then return the count in XML format.
Free 70-451 exam prep:
Q67. You intend to use SQL Server 2008 to create a database solution. The full-text search component is installedin the database which supports a Web site.
You intend to create a table named Courses which has the structure above. On the basis of the CourseTitlefield, users of the Web site will search for courses. When the search is launched for a course by a user, a full-text must be constructed to ensure the compliances below:
when the exact search phrase is found, rows are returned;
rows are in order of how well they match with the search phrase.
So what should you specify in the full-text query?
A. A CONTAINS predicate
B. A FREETEXT predicate
C. A CONTAINSTABLE function
D. A FREETEXTTABLE function
Q68. You have a table that has five varchar columns. You are designing an application that requires data in well-formed XML. You need to design a query statement that will produce the data in well-formed XML. Whatshould you use?
A. FOR XML PATH
C. XPATH query
D. XSD schema
Q69. You have a table that contains 5 million rows. The table has a clustered index. You plan to add an additionalindex 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 storagespace required for the index. Which type of index should you use?
Q70. You are a database developer. You plan to design a database solution by using SQL Server 2008. Thedatabase contains a table named Claims. The structure of the Claims table is as shown in the following table.
Only two percent of the claims are open at any point in time. You discover that queries on claims that have anOpen status take a long time to execute. You need to optimize the performance of the claim-processingqueries. What should you do?
A. Use a partitioning function to partition the Claims table on the open_date column.
B. Create a view for the Claims table by using a WHERE clause to include all rows that have a NULL valueinthe close_date column.
C. Create an index for the Claims table by using a WHERE clause to include all rows that have a NULL valuein the close_date column.
D. Create a table-valued function for the Claims table by using a WHERE clause to include all rows thathavea NULL value in the close_date column.
Q71. You are a database developer. You plan to design a database solution by using SQL Server 2008. A table in adatabase will store large image files (20-50 MB in size). You have the following business requirements:
The image files are accessible by applications that use Win32 APIs.
The image files are part of the database backup.
You need to identify an appropriate strategy to store the image files. Which strategy should you use?
A. Use an image data type.
B. Use the varbinary(max) data type.
C. Use the varbinary(max) data type along with the FILESTREAM attribute.
D. Store the image file in a file system. Use a varchar data type to store the file location in the database.
Q72. You are a database developer. You plan to create a database by using SQL Server 2008. A database containsa table named Sales. The Sales table contains customer order summary information. You create a storedprocedure that uses a SELECT statement. At the moment of execution, the procedure must return a precisesummation of the total sales for the current day. You need to use a query hint to prevent any data modificationin the Sales table when the stored procedure is being executed. Which query hint should you recommend?