Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 101:

  You are the database developer for an order-processing application. The database has the following tables:

  

  You need to ensure that the following requirements are met:

  Data is loaded into the tables.

  Data that has been inserted will be removed if any statement fails. No open transactions are performed after the batch has executed.

  Which Transact-SQL statements should you use?

  

  A. B.
  C. D.
  B

• Question 102:

  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?

  A. Preview Workload Table
  B. Export Session Results
  C. Import Session Definition
  D. Export Session Definition
  B. Export Session Results

• Question 103:

  You have a database that contains two tables named ProductCategory and ProductSubCategory. You need to write a query that returns a list of product categories that contain more than ten sub- categories.

  Which query should you use?

  A. SELECT [Name] FROM ProductSubCategory WHERE ProductCategoryID IN ( SELECT ProductCategoryID FROM ProductCategory) GROUP BY [Name] HAVING COUNT(*) > 10
  B. SELECT [Name] FROM ProductSubCategory WHERE ProductCategoryID NOT IN (SELECT ProductCategoryID FROM ProductCategory) GROUP BY [Name] HAVING COUNT(*) > 10
  C. SELECT [Name] FROM Product Category c WHERE EXISTS (SELECT ProductCategoryID FROM ProductSubCategory WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID HAVING COUNT(*) > 10)
  D. SELECT [Name] FROM Product Category c WHERE NOT EXISTS (SELECT ProductCategoryID FROM ProductSubCategory WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID HAVING COUNT(*) > 10)
  C. SELECT [Name] FROM Product Category c WHERE EXISTS (SELECT ProductCategoryID FROM ProductSubCategory WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID HAVING COUNT(*) > 10)

• Question 104:

  You administer a Microsoft SQL Server 2008 R2 instance configured to use Windows Authentication. The database contains a table named CustomerTransaction that has the following definition:

  

  You define the following table:

  

  You need to ensure that the following requirements are met:

  An entry is logged in CustomerWarningLog when a customer's account balance is less than 100.00. The TransactionLogUserName is set to the login name of the user who modifies the CustomerTransaction table.

  Which Transact-SQL statement or statements should you use?

  

  A. B.
  C. D.
  D

• Question 105:

  You want to delete a User-Defined Function (UDF) named ufnGetProductListPrice from the AdventureWorks2008R2 database. You need to perform the following

  tasks before executing DROP FUNCTION on ufnGetProductListPrice:

  View a list of objects that depend on ufnGetProductListPrice. View a list of objects on which ufnGetProductListPrice depends.

  What should you do? (To answer, select the appropriate option or options in the answer area.)

  Hot Area:

  

  

• Question 106:

  You have multiple tables that represent properties of the same kind of entities. The property values are comprised of text, geometry, varchar(max), and user- defined types specified as 'bit NOT NULL' data types.

  You plan to consolidate the data from multiple tables into a single table. The table will use semi- structured storage by taking advantage of the SPARSE option. You are tasked to identify the data types that are compatible with the SPARSE

  option.

  Which data type is compatible with the SPARSE option?

  A. text
  B. geometry
  C. varchar(max)
  D. A user-defined type defined as 'bit NOT NULL'
  C. varchar(max)

  ---

  Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. The following data types cannot be specified as SPARSE: geography geometry image next text timestamp user-defined data types

• Question 107:

  You have a SQL Server 2008 database. You have not installed a MAPI client. You need to send e-mail from a stored procedure.

  Which system stored procedure should you use?

  A. xp_sendmail
  B. xp_startmail
  C. sp_send_dbmail
  D. sysmail_start_sp
  C. sp_send_dbmail

• Question 108:

  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 the Sales table is 10. You need to write a query to export only sales data that changed since version 10, including the primary key of deleted rows.

  Which method should you use?

  A. FROM Sales RIGHT JOIN CHANGETABLE (CHANGES Sales, 10) AS C ...
  B. FROM Sales INNER JOIN CHANGETABLE (CHANGES Sales, 10) AS C ...
  C. FROM Sales INNER JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C ...
  D. FROM Sales RIGHT JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C ...
  A. FROM Sales RIGHT JOIN CHANGETABLE (CHANGES Sales, 10) AS C ...

• Question 109:

  Click the Exhibit button.

  

  You are developing a database using Microsoft SQL Server 2008. The database contains the tables shown in the exhibit.

  You are required to prevent parts from being deleted if they belong to a kit. If a part belongs to a kit, the delete should not occur and the IsDeleted column for the row should be changed to 'True'. Parts can be deleted if they do not belong to a

  kit.

  You have the following Transact-SQL statement to be used in a trigger:

  

  You need to implement the Transact-SQL statement in a trigger.

  Which trigger syntax should you use?

  A. CREATE TRIGGER tr_Part_d ON Part AFTER DELETE AS BEGIN ... END
  B. CREATE TRIGGER tr_Part_d ON Part INSTEAD OF DELETE AS BEGIN ... END
  C. CREATE TRIGGER tr_KitPart_d ON KitPart AFTER DELETE AS BEGIN ... END
  D. CREATE TRIGGER tr_KitPart_d ON KitPart INSTEAD OF DELETE AS BEGIN ... END
  B. CREATE TRIGGER tr_Part_d ON Part INSTEAD OF DELETE AS BEGIN ... END

• Question 110:

  You need to create a column that allows you to create a unique constraint.

  Which two column definitions should you choose? (Each correct answer presents a complete solution. Choose two.)

  A. nvarchar(100) NULL
  B. nvarchar(max) NOT NULL
  C. nvarchar(100) NOT NULL
  D. nvarchar(100) SPARSE NULL
  A. nvarchar(100) NULL
  C. nvarchar(100) NOT NULL