70-451 Exam Questions

You have a server that runs SQL Server 2008. The server supports an online application. New products are constantly being added to a database stored on the server. You identify the...

You review the execution plan for a query that contains a join. The execution plan contains a RID Lookup. You need to reduce the I/O that is generated by the RID Lookup. Which inde...

You have an execution plan as shown in the exhibit. (Click the Exhibit button.) The indexes and keys for the table are as follows: - Unique index on Col1 and CoI2 - Primary key of...

You have a server that runs SQL Server 2008 Enterprise. You have a query that contains SUM, AVG, and MAX functions. You discover that the query takes a long time to execute. You ne...

You have production tables that are based on data from staging tables. You plan to create stored procedures that will perform the following actions on the production tables: - Inse...

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 dete...

You have a database that has 20 tables. The tables are not configured to have any referential integrity. All tables have primary keys. Each table contains over 5 million rows. Ten...

You are the administrator of a SQL Server 2008 instance with a database named DB1. The latest differential backup is performed at 15:30, the full back up was performed at 13:30, an...

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a large non-partitioned table with 4 million rows. You write a st...

You need to design a solution to guarantee that a stored procedure is part of every transaction. What should you do?

You have a line-of-business application. The application generates reports based on data from database views. Users report that it takes a long time to generate reports. You run a...

You are a database developer. You plan to design a database solution by using SQL Server 2008. You are writing a query that returns XML data from a table in a database. You need to...

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: - The database is...

You need to develop a stored procedure strategy that will manipulate temporary result sets to meet the following requirements: - Stored procedures must receive result sets as param...

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a view. The view will contain a computed column that will b...

You are developing a security policy for the SQL Server developers in your organization. You need to create a security strategy to protect Transact-SQL applications from SQL inject...

You are a database developer. You plan to design a database solution by using SQL Server 2008. Your company operates a fulfillment warehouse. The company has the following business...

You have a table that contains a string column. Most of the rows have the same value. You need to create a multistep stored procedure that meets the following requirements: - Accep...

You are designing a CLR stored procedure that will access a remote Web service. You need to ensure that the CLR stored procedure can access the remote Web service. Which two action...

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 so...

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...

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 frequ...

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...

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...

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 a...

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...

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 t...

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 ident...

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?

You are a database developer. You plan to create a database by using SQL Server 2008. The database has a table named Employees. The table contains records of employees and their ma...

You have a table that contains an XML column named XMLDatal. The column contains the following nodes: - Title - Description - Author - Weight - NumPages You need to design a stored...

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a large table that has 20 million rows. The table contains the...

You have a server that runs SQL Server 2008 Standard. The server provides data storage for a third- party contact management application. A service-level agreement for the applicat...

You are designing a database that will be used for reporting purposes. You need to minimize the data storage requirements and improve the application response time. What should you...

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 ta...

You are designing a table that will store transactions for an online retailer. You anticipate that the online retailer will have an average of 500,000 transactions per day. You nee...

You have a database that has 20 large tables. All the tables have qualified indexes. As the tables grow, you discover that queries that contain JOIN statements execute more slowly....

You have an office in Tokyo and an office in New York. Each office contains a server that runs SQL Server 2008 Enterprise. The New York office contains a central database that is m...

You are using SQL Server Profiler to gather deadlock information. You need to capture an XML description of a deadlock. Which event should you use?

You are designing a data storage solution for a transactional application. You need to ensure that each row in a table records the date and the time that the row was written. The t...

You need to create an application that will represent the relationship between managers and employees. You must achieve this goal by using the minimum amount of tables. What should...

You are creating a database that will store usernames and passwords for an application. You need to recommend a solution to store the passwords in the database. What should you rec...

You are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify which transaction causes the deadlock. What should you d...

You are designing a database named DB1. Changes will be deployed to DB1 every Wednesday night. You need to recommend a strategy to deploy the changes to DB1. The strategy must meet...

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 cla...

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 tabl...

You are creating a new update query. You need to ensure that the query terminates if a shared lock is encountered white the data is updating. What should you include in the query?