Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 141:

  You are working with a SQL Server 2008 instance that is configured to use the Icelandic_CS_AS collation. You create a database by using code that includes the following statements.

  

  You need to identify the collation that will be assigned to #AppTempTable. Which collation will be assigned?

  A. Japanese_CS_AS
  B. Icelandic_CS_AS
  C. The collation selected by the Windows system locale of the server
  D. No collation
  B. Icelandic_CS_AS

• Question 142:

  You have a table named JobCandidate. You are tasked to delete a row in the JobCandidate table.

  You need to write a transaction that allows the database to be restored to the exact point the record was deleted without knowing the time of execution.

  Which query should you use?

  A. BEGIN TRANSACTION DELETE FROM JobCandidate WHERE JobCandidateID = 10; COMMIT TRANSACTION;
  B. BEGIN TRANSACTION WITH MARK N'Deleting a Job Candidate'; DELETE FROM JobCandidate WHERE JobCandidateID = 10; COMMIT TRANSACTION
  C. BEGIN TRANSACTION Delete_Candidate WITH MARK DELETE FROM JobCandidate WHERE JobCandidateID = 10; COMMIT TRANSACTION Delete_Candidate;
  D. DECLARE @CandidateName varchar(50) = 'Delete_Candidate' BEGIN TRANSACTION @CandidateName DELETE FROM JobCandidate WHERE JobCandidateID = 10; COMMIT TRANSACTION @CandidateName;
  C. BEGIN TRANSACTION Delete_Candidate WITH MARK DELETE FROM JobCandidate WHERE JobCandidateID = 10; COMMIT TRANSACTION Delete_Candidate;

  ---

  BEGIN { TRAN | TRANSACTION }

  [ { transaction_name | @tran_name_variable }

  [ WITH MARK [ 'description' ] ]

  ]

  [ ; ]

  WITH MARK [ 'description' ] - specifies that the transaction is marked in the log. description is a string that describes the mark. If WITH MARK is used, a transaction name must be specified. When restoring a database to an earlier state, the

  marked transaction can be used in place of a date and time. The mark is placed in the transaction log only if the database is updated by the marked transaction. Transactions that do not modify data are not marked. BEGIN TRAN new_name

  WITH MARK can be nested within an already existing transaction that is not marked.

  Upon doing so, new_name becomes the mark name for the transaction, despite the name that the transaction may already have been given.

• Question 143:

  You administer a Microsoft SQL Server 2008 database that contains a table named dbo.SalesOrders.

  The table has the following definition:

  

  The SalesOrder table contains one million rows.

  You want to create a report that meets the following requirements:

  Only the states of the Unites States are ranked against each other based on the total number of orders received from each state. When two states have the same rank, the rank of the subsequent state is one plus the number of ranks that

  come before that row, as shown in the exhibit. (Click the Exhibit button.)

  

  You need to execute a Transact-SQL query to generate the report. Which Transact-SQL query should you use?

  

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

• Question 144:

  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 observe that the average fragmentation for the

  AK_Product_ProductNumber index is 24 percent.

  You need to reduce fragmentation. You need to achieve this goal without blocking access to table data.

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

• Question 145:

  You administer a database named Contoso running on a Microsoft SQL Server 2008 R2 instance.

  You plan to implement custom error handling in your application.

  You need to implement custom error handling that meets the following requirements:

  The custom message is a reusable user-defined error message. The message returned to the application is an informational message that returns status

  information or error that is not severe.

  The custom message returned indicates that an error has occurred in the current database and current session.

  Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 146:

  You are a database developer writing reports for a sales management application. A customer table has the following definition:

  

  An order table has the following definition:

  

  You need to write a report that contains the following columns:

  

  You also need to ensure that the report meets the following requirements: Contains only customers who have placed orders Contains only customers who have Postal Codes beginning with 89 Returns only one record for each customer The report is ordered by the CustomerID in ascending order

  Which Transact-SQL query should you use?

  

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

• Question 147:

  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'

  0

  1 1

  1 1

  '

  DECLARE @OutputAgeOfCoffeeDrinkers XML

  SET @OutputAgeOfCoffeeDrinkers = @CustomerDemographics.query(' for $output in /child::CustomerDemographics/child::Customer[ ( child::IsCoffeeDrinker[1] cast as xs:boolean )]

  return

  { $output/attribute::Age \}

  ')

  SELECT @OutputAgeOfCoffeeDrinkers

  You need to determine the result of the query.

  What result should you expect?

  A

• Question 148:

  You need to identify which products will be inserted when you execute the following code block.

  

  Which products will be inserted?

  A. Beverage
  B. Food and beverage
  C. None
  D. Food
  C. None

• Question 149:

  You have a computed column that is implemented with a user-defined function. The user-defined function returns a formatted account number. The column must be indexed to provide adequate search performance. You plan to create an index on the computed column. You need to identify the valid combination of ObjectPropertyEX values for the user-defined function.

  Which combination should you use?

  A. IsDeterministic = True IsSystemVerified = True UserDataAccess = False SystemDataAccess = False
  B. IsDeterministic = True IsSystemVerified = True IsPrecise = True IsTableFunction = True
  C. IsDeterministic = False IsSystemVerified = True UserDataAccess = False SystemDataAccess = False
  D. IsDeterministic = False IsSystemVerified = True IsPrecise = True SystemDataAccess = False
  A. IsDeterministic = True IsSystemVerified = True UserDataAccess = False SystemDataAccess = False

• Question 150:

  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(1,1) NOT NULL PRIMARY KEY CLUSTERED, [OrderDate] [datetime] NOT NULL, [CustomerID] [int] NOT NULL,

  [SalesPersonID] [int] NULL,

  [CommentDate] [date] NULL);

  This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:

  SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales

  WHERE CommentDate IS NOT NULL

  AND SalesPersonID IS NOT NULL;

  You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the query. The index must conserve

  disk space while optimizing your query.

  Which index should you create?

  A. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (CustomerID) INCLUDE (CommentDate,SalesPersonID);
  B. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (SalesPersonID) INCLUDE (CommentDate,CustomerID);
  C. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (CustomerID) INCLUDE(CommentDate) WHERE SalesPersonID IS NOT NULL;
  D. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (CommentDate, SalesPersonID) INCLUDE(CustomerID) WHERE CommentDate IS NOT NULL;
  D. CREATE NONCLUSTERED INDEX idx1 ON dbo.Sales (CommentDate, SalesPersonID) INCLUDE(CustomerID) WHERE CommentDate IS NOT NULL;