70-433 Exam Questions

You have a table named Inventory. You open a Microsoft Windows PowerShell session at the following location by using the SQL Server Windows PowerShell provider. PS SQLSERVER:\SQL\C...

You are configuring Service Broker to process messages within a single database. You have performed the following steps. CREATE MESSAGE TYPE CREATE CONTRACT CREATE QUEUE You need t...

You have a database named Contoso. The Contoso database has a Service Broker queue named VacationRequestQueue. The Contoso database has been restored to a new server. Since restori...

You created a Service Broker queue by using the following Transact-SQL statement: CREATE QUEUE VacationRequestQueue WITH RETENTION = OFF, ACTIVATION ( PROCEDURE_NAME = dbo.Vacation...

You use the same Service Broker configuration to support a Web site and an internal application. The Web site generates a greater workload than the internal application. You need t...

You are using Microsoft SQL Server 2008 Enterprise Edition. You need to maintain a history of all data modifications made to a table, including the type of modification and the val...

A database contains tables named Sales and SalesArchive. SalesArchive contains historical sales data. You configure Change Tracking on the Sales table. The minimum valid version of...

You are required to modify a table named Sales.SalesOrder. The table has change tracking enabled on it. You need to disable change tracking prior to modifying the Sales.SalesOrder...

You have implemented change tracking on a table named Sales.SalesOrder. You need to determine all columns that have changed since the minimum valid version. Which function should y...

You have two tables named Customers and Orders. They are related by a foreign key constraint on the CustomerID on each table. You need to generate the following XML structure that...

You need to generate the following XML document. <ProductExport> <Product Price="99">Product1</Product> <Product Price="199">Product2</Product> <Product Price="299">Product3</Produ...

Your company's database contains Customers and Orders tables. You have been tasked to write a SELECT statement that outputs customer and order data as a valid and well-formed XML d...

Your company's database contains Customers and Orders tables. You have been tasked to write a SELECT statement that exposes the data as a valid and well- formed XML document. The X...

You have a table named Customer that has an XML column named Locations. This column stores an XML fragment that contains details of one or more locations, as show in the following...

Click the Exhibit button. You have the following XML: </Site> </Site> </Site> You are tasked to query the sites listed in the XML by using OPENXML. The results will have two column...

Your company uses an application that passes XML to the database server by using stored procedures. The database server has a large number of XML handles that are currently active....

You work for a company that provides marketing data to other companies. You have the following Transact-SQL statement: DECLARE @CustomerDemographics XML SET @CustomerDemographics=N...

You have a table named Stores that has an XML column named OpenHours. This column contains the opening and closing times. <hours dayofWeek= "Monday" open ="8:00 AM" closed="8:00 PM...

You have the following XML document that contains Product information. DECLARE @prodList xml =' <ProductList xmlns="urn:Wide_World_Importers/schemas/Products"> <Product Name="Produ...

You have a table named Products.Product. The table has columns ProductID, Name, Size, and Category. You have a variable named @XML with following XML value: <Root> <Category Name="...

Your company exchanges information with other companies by using XML and Web services. Your manager asks you to remove a schema collection that is no longer used. Before dropping t...

You have an XML schema that you must use to validate XML data in your database. You need to store this XML schema. Which code segment should you use?

You have a table named Customers that has an XML column named CustomerData. There are currently no indexes on the table. You use the following WHERE clause in a query: WHERE Custom...

You need to capture the execution plan for a query. Which statement should you use?

You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB02 hosting the Inventory database. The Inventory database has two tables named Sa...

You are troubleshooting query performance on SQL Server 2008. You are tasked to capture a graphical execution plan. You need to save the plan to a file that can be used by SQL Serv...

You have run a server side trace that created 45 trace files. You want to load the trace files on your workstation in a database table called PerfData for further analysis. ou need...

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 troubleshooting query performance on SQL Server 2008. You have profiler trace data in a table named PerfData. You need to determine which events are taking longer than one...

You are using the Database Engine Tuning Advisor (DTA) to analyze a workload. You need to save the recommendations generated by the DTA. Which command should you use?

You need to capture and record a workload for analysis by the Database Engine Tuning Advisor (DTA). Which tool should you use?

You have a database that uses stored procedures to perform INSERT, UPDATE, DELETE, and SELECT statements. You are tasked with providing a recommendation of indexes to be created an...

You are tasked with creating a workload that will be used by the Database Engine Tuning Advisor (DTA). You need to create a workload in an appropriate format. Which format should y...

You need to build CREATE INDEX statements for all the missing indexes that SQL Server has identified. Which dynamic management view should you use?

You notice that a database server is responding slowly to queries. You run the following dynamic management views (DMV) query on the server. SELECT TOP (10) wait_type, wait_time_ms...

You attempt to query sys.dm_db_index_usage_stats to check the status on the indexes in the Contoso database. The query fails and you receive the following error: "The user does not...

You have been tasked to delete a number of Database Mail messages that have been sent. You need to delete all the emails that were sent more than one month ago. Which Transact-SQL...

You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition: CREATE TABLE [dbo].[Sales]( [SalesID] [int] IDENTITY(...

You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Inventory database. ABC.com has a user named Rory Allen has the...

You are creating a new table in a database. Your business requires you to store data in the table for only seven days. You need to implement a partitioned table to meet this busine...

You have a table named Customer. You need to ensure that customer data in the table meets the following requirements: credit limit must be zero unless customer identification has b...

You have been tasked to delete 1000 rows from a table named NewWidgets. There are 2000 rows in which the column ToBeDeleted set to 1. You need to write a Transact-SQL batch that wi...

You work for an international charity organization. You are writing a query to list the highest 100 different amounts that were donated. You have written the following code segment...

You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Inventory database and transaction files. You notice that there...

You work as a network database administrator at ABC.com. ABC.com has a database server named ABC-DB01 that hosts the Inventory database. You need to use an explicit transaction to...

You have a user named John. He has SELECT access to the Sales schema. You need to eliminate John's SELECT access rights from the Sales.SalesOrder table without affecting his other...

You are the administrator of a SQL Server 2008 instance with a database named DB1. A backup of DB1 is performed every day. You have to minimize the size of the full database backup...

You are the administrator of a SQL Server 2008 instance with a database named DB1. When you are absent, a user will use a login named Mary to log in and maintain the database snaps...

You are the administrator of a SQL Server 2008 instance. Users report that they are unable to connect to the named instances. You check and verify that they can only connect to the...

You are a database developer for your company. You are developing a stored procedure that must accept a parameter of the xml data type and store the data in a relational table stru...