Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 161:

  You have a table named AccountsReceivable. The table has no indexes. There are 75,000 rows in the table. You have a partition function named FG_AccountData. The AccountsReceivable table is defined in the following Transact-SQL

  statement:

  CREATE TABLE AccountsReceivable (

  column_a INT NOT NULL,

  column_b VARCHAR(20) NULL)

  ON [PRIMARY];

  You need to move the AccountsReceivable table from the PRIMARY file group to FG_AccountData.

  Which Transact-SQL statement should you use?

  A. CREATE CLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON [FG_AccountData];
  B. CREATE NONCLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON [FG_AccountData];
  C. CREATE CLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON FG_AccountData(column_a);
  D. CREATE NONCLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON FG_AccountData(column_a);
  C. CREATE CLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON FG_AccountData(column_a);

• Question 162:

  Your database contains Products and Orders tables. You need to write a query which return ProductID of the products which have not been placed in any order.

  Which operator can you use?

  A. Union
  B. Union ALL
  C. Intersect
  D. Except
  D. Except

  ---

  EXCEPT returns any distinct values from the left query that are not also found on the right query.

  * Example: The following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query. USE AdventureWorks2012; GO SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder ; --Result: Products without work orders

• Question 163:

  You administer a Microsoft SQL Server 2008 database that contains two tables named Products and Suppliers. You want to implement referential integrity

  between the Products and Suppliers tables. You want to create a new Foreign Key constraint on the Products table.

  The new Foreign Key constraint must meet the following requirements:

  It must be created on the SupplierID column on the Products table to refer to the SupplierID column on the Suppliers table.

  It must prevent the deletion of rows from the Suppliers table whenever an attempt is made to delete a Supplier by using a SupplierID referenced by Foreign Keys

  in existing rows in the Products table.

  You need to be able to create a new Foreign Key constraint by using Microsoft SQL Server Management Studio. What should you do? (To answer, configure the

  appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 164:

  You have a database server that has four quad-core processors. This database server executes complex queries that are used to generate reports.

  You need to force a query to use only one processor core without affecting other queries.

  Which option should you use?

  A. OPTION (FAST 1)
  B. OPTION (MAXDOP 1)
  C. OPTION (RECOMPILE)
  D. OPTION (MAXRECURSION 1)
  B. OPTION (MAXDOP 1)

• Question 165:

  You administer a SQL Server 2008 instance. The instance contains a database table named Sales.SalesOrderDetail. The table has the following definition:

  

  You need to enable row compression for the index.

  Which Transact-SQL statement or statements should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

• Question 166:

  You are creating a function that references a table. You need to prevent the table from being dropped.

  Which option should you use when you create the function?

  A. WITH ENCRYPTION
  B. WITH EXECUTE AS
  C. WITH SCHEMABINDING
  D. WITH RETURNS NULL ON NULL INPUT
  C. WITH SCHEMABINDING

• Question 167:

  You administer a Microsoft SQL Server 2008 R2 database instance named AdventureWorks. A user who has the db_datareader permissions on the AdventureWorks database wants to view the estimated execution plan XML output document for the following query: SELECT * FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 1 AND SubTotal > 500 You need to ensure that the user can view the document. Which two actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 168:

  You need to alter stored procedures to use the WITH RECOMPILE option.

  Which types of stored procedures should you alter? (Each correct answer represents a complete solution. Choose two.)

  A. Stored procedures implemented from CLR assemblies.
  B. Stored procedures that require the FOR REPLICATION option.
  C. Stored procedures that require the WITH ENCRYPTION option.
  D. Stored procedures that contain queries that use the OPTION (RECOMPILE) hint.
  C. Stored procedures that require the WITH ENCRYPTION option.
  D. Stored procedures that contain queries that use the OPTION (RECOMPILE) hint.

  ---

  As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes. SQL Server provides three ways to force a stored procedure to recompile: The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run. Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled every time that it is executed. You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. A. CREATE and ALTER PROCEDURE syntax for CLR Stored Procedure does not have RECOMPILE option. B. The RECOMPILE option is ignored for procedures created with FOR REPLICATION. C. ENCRYPTION option and RECOMPILE option can go together.

• Question 169:

  You administer a Microsoft SQL Server 2008 database named AdventureWorks that contains a table named Production.Product. The table contains a primary key named PK_Product_ProductID and a non-clustered index named AK_Product_ProductNumber. Both indexes have been created on a single primary partition.

  The table has the following definition:

  

  You want to ensure that data retrieval takes the minimum amount of time when the queries executed against the Production.Product table are ordered by product number or filtered by class.

  You need to find out the degree of fragmentation for the indexes on the Production.Product table.

  Which Transact-SQL statement should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  F. Option F
  G. Option G
  H. Option H
  I. Option I
  J. Option J
  L

• Question 170:

  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 restoring the database, Service Broker is no longer

  able to send new messages.

  You need to configure Service Broker in order to resolve the issue.

  Which Transact-SQL statement should you use?

  A. ALTER DATABASE Contoso SET NEW_BROKER;
  B. ALTER DATABASE Contoso SET ENABLE_BROKER;
  C. ALTER QUEUE VacationRequestQueue WITH STATUS = ON;
  D. ALTER QUEUE VacationRequestQueue WITH ACTIVATION (STATUS = ON);
  A. ALTER DATABASE Contoso SET NEW_BROKER;