Microsoft 70-451 Real Exam Questions

PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008

1: 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.
Correct Answers: D

2: 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 data warehouse contains a table named factBuyingHabits. The factBuyingHabits table has no indexes.  You need to synchronize data between the two tables on a weekly basis. The synchronization process has the following requirements:
   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 factBuyingHabits table.
   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 minimum amount 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 run this package.
B.Design a single SSIS package that uses the Slowly Changing Dimension task. Schedule a job to run this package.
C.Write one stored procedure that contains a MERGE statement to perform the INSERT, UPDATE, and DELETE 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 to run the stored procedures in a sequential manner.
Correct Answers: C

3: You are a database developer. You plan to design a database solution by using SQL Server 2008. You create a stored procedure that uses the TRY/CATCH syntax in a new database. When the stored procedure is executed, it logs information about each step in the TRY block into a table named dbo.ExecutionLog. When an error occurs, the stored procedure must perform the following tasks:
   Roll back the changes made to the target tables.
   Retain the log entries stored in the dbo.ExecutionLog table.
You need to ensure that the stored procedure performs the given tasks.
What should you do?
A.Start a transaction in the TRY block.
After each step, insert log entries into the dbo.ExecutionLog table.
In the CATCH block, commit the transaction.
After the CATCH block, use data in the dbo.ExecutionLog table to reverse any changes made to the target tables.
Commit the transaction if one exists.
B.Start a transaction in the TRY block.
Before each step, define a transactional save point.
After each step, insert log entries into the dbo.ExecutionLog table.
In the CATCH block, roll back to the transactional save points.
After the CATCH block, commit the transaction.
C.Define a temporary table before the TRY block by using the same columns as that of the dbo.ExecutionLog table.
Start a transaction in the TRY block.
After each step, insert log entries into the temporary table.
In the CATCH block, roll back the transaction.
After the CATCH block, insert the rows from the temporary table into the dbo.ExecutionLog table.
Commit the transaction if one exists.
D.Define a table variable before the TRY block by using the same columns as that of the dbo.ExecutionLog table.
Start a transaction in the TRY block.
After each step, insert log entries into the table variable.
In the CATCH block, roll back the transaction.
After the CATCH block, insert the rows from the table variable into the dbo.ExecutionLog table.
Commit the transaction if one exists.
Correct Answers: D

4: You are a SQL Server 2008 developer. You create an online transaction processing (OLTP) database by using SQL Server 2008 in an enterprise environment. The database contains a table named SalesDetails. Each record in the table contains data in any one of the following pairs of nullable columns:
   InternetSalesTargets and InternetSales
   ResellerSalesTargets and ResellerSales
   ForeignSalesTargets and ForeignSales
The table also contains three NOT NULL key columns. A large number of records are inserted on a daily basis into the SalesDetails table.
Summary reports are generated from the SalesDetails table. Each report is based on aggregated data from any one of the pairs of nullable columns.
You need to design a view or views to meet the following requirements:
   The SalesDetails table cannot be directly modified.
   The performance of the reports is maximized.
   The amount of storage space for each report is minimized.
What should you do?
A.Create an indexed view from the SalesDetails table that contains aggregated data of all the columns required by all the reports.
B.Create multiple indexed views from the SalesDetails table so that each view contains aggregated data of only the columns required by the respective report.
C.Create multiple Report tables from the SalesDetails table so that each Report table contains aggregated data of only the columns required by the respective report. Create views on top of each of the Report tables.
D.Perform a quick transfer of aggregated new records to a staging table at the end of each month. Create an indexed view from the staging table that contains aggregated data of all the columns required by all the reports.
Correct Answers: B

5: You are a database developer. You plan to design a database solution by using SQL Server 2008. You have a database that contains a table and a table-valued function. The table-valued function accepts the primary key from the table as a parameter.  You plan to write a query that joins the table to the results of the table-valued function.  You need to ensure that only rows from the table that produce a result set from the table-valued function are returned. Which join predicate should you use?
A.CROSS APPLY
B.OUTER APPLY
C.INNER JOIN
D.LEFT OUTER JOIN
Correct Answers: A

6: You are a database developer. You plan to design a database solution by using SQL Server 2008.
A database contains a view that has the following features:
   It contains a WHERE clause that filters specific records.
   It allows data updates.
You need to prevent data modifications that do not conform to the WHERE clause. You want to achieve this goal by using minimum effort.
What should you do?
A.Create an INSTEAD OF trigger on the view.
B.Create a unique clustered index on the view.
C.Alter the view by adding the WITH CHECK OPTION clause.
D.Alter the view by adding the WITH SCHEMABINDING clause.
Correct Answers: C

7: You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. You plan to create a stored procedure that queries a sales table and produces forecast data.  You do not have administrative permissions, and you are not the owner of the database. You have permissions to create stored procedures. Users will only have permissions to execute your stored procedures.  You need to ensure that users can execute the stored procedures. What should you do?
A.Set the TRUSTWORTHY property of the database to ON.
B.Include an EXECUTE AS OWNER clause when you create each stored procedure.
C.Include an EXECUTE AS CALLER clause when you create each stored procedure.
D.Include a SETUSER statement before you query the sales table in each stored procedure.
Correct Answers: B

8: You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a common language runtime (CLR) user-defined scalar function. The function will return an integer value.  You need to ensure that the computed columns that use the result from this function can be indexed.  What should you do?
A.Ensure that the logic of the function returns the same value for the same input values and the same database state.
Ensure that the IsDeterministic property is set to True.
B.Ensure that the logic of the function returns a different value for the same input values and the same database state.
Ensure that the IsDeterministic property is set to True.
C.Ensure that the logic of the function returns the same value for the same input values and the same database state.
Ensure that the IsDeterministic property is set to False.
D.Ensure that the logic of the function returns a different value for the same input values and the same database state.
Ensure that the IsDeterministic property is set to False.
Correct Answers: A

9: You are a database developer. You plan to design a database solution by using SQL Server 2008. There are two schemas named Sales and Marketing. You are the owner of the Sales schema and the Marketing schema is owned by a user named MarketingUser. Users of the Marketing schema do not have permissions to access the Sales schema. You have permissions to create objects in all schemas in the database. The Sales schema has a table named Customers. You plan to create a stored procedure in the Marketing schema for the marketing team. The stored procedure will select data from the Customers table and will be owned by MarketingUser. You need to ensure that the marketing team is able to execute the stored procedure. What should you do?
A.Create the procedure by using the EXECUTE AS SELF option.
B.Create the procedure by using the EXECUTE AS CALLER option.
C.Create the procedure by using the EXECUTE AS OWNER option.
D.Create the procedure by using the EXECUTE AS USER=MarketingUser option.
Correct Answers: A

10: 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.
Correct Answers: D

Download  |  Password: certificatexam.com