Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 71:

  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 (MAXDOP 1)
  B. OPTION (MAXDOP 16)
  C. OPTION (MAXDOP 0)
  D. OPTION (MAXDOP 4)
  A. OPTION (MAXDOP 1)

• Question 72:

  You notice that for a particular set of parameter values the following query sometimes executes quickly and other times executes slowly. You also notice that 90 percent of the rows in the Address table contain the same value for the city.

  SELECT AddressId, AddressLine1, City, PostalCode FROM Person.Address WHERE City = @city_name AND PostalCode = @postal_code

  You need to use a query hint that, for the particular set of parameter values, will result in a more consistent query execution time.

  Which query hint should you use?

  A. FAST
  B. MAXDOP
  C. OPTIMIZE FOR
  D. PARAMETERIZATION FORCED
  C. OPTIMIZE FOR

• Question 73:

  You are reviewing a trigger in the database which was deployed with the folowing script:

  EXECUTE AS USER = 'BuildUser' GO CREATE TRIGGER Inventory.TR_Stock ON Inventory.Stock FOR INSERT, UPDATE, DELETE EXECUTE AS SELF AS ...

  A user 'WebUser' insert rows into Inventory.Stock table. You need to identify under which security context the trigger will execute.

  A. DBO
  B. Inventory
  C. WebUser
  D. BuildUser
  D. BuildUser

• Question 74:

  Your company manufactures and distributes bicycle parts. You have a full-text catalog on the Inventory table which contains the PartName and Description columns. You also use a full-text thesaurus to expand common bicycle terms.

  You need to write a full-text query that will not only match the exact word in the search, but also the meaning.

  Which Transact-SQL statement should you use?

  A. SELECT * FROM Inventory WHERE FREETEXT (*, 'cycle'))
  B. SELECT * FROM Inventory WHERE CONTAINS (*, 'cycle')
  C. SELECT * FROM Inventory WHERE Description LIKE '%cycle%'
  D. SELECT * FROM Inventory WHERE CONTAINS (*, 'FormsOf(Inflectional, cycle)')
  A. SELECT * FROM Inventory WHERE FREETEXT (*, 'cycle'))

• Question 75:

  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:

  

  The index has the following definition:

  

  

  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
  Q

• Question 76:

  You administer a Microsoft SQL Server 2008 database that contains a stored procedure named dbo.SalesOrderDetails. The stored procedure has following definition:

  

  Parameter values passed to the stored procedure largely vary.

  You discover that the stored procedure executes quickly for some parameters but slowly for other parameters.

  You need to ensure that the query plan generated is optimized to provide the most consistent execution times for any set of parameters passed to the stored procedure.

  A. OPTION (NOLCCK)
  B. OPTION (KEEP PLAN)
  C. OPTION (ROBUST PLAN)
  D. OPTION (RECOMPILE)
  C. OPTION (ROBUST PLAN)

• Question 77:

  You need to round the value 1.75 to the nearest whole number.

  Which code segment should you use?

  A. Select ROUND(1.75,0)
  B. Select ROUND(1.75,2)
  C. Select ROUND(1.75,1.0)
  D. Select ROUND(1.75,2.0)
  A. Select ROUND(1.75,0)

• Question 78:

  You are the database developer for a Microsoft SQL Server 2008 database that contains tables named order and product. The tables have the following definitions:

  

  You need to write a query that will extract a valid XML result set of all ordered products. You also need to ensure that the query conforms to the following schema:

  

  Which SQL query should you use?

  

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

• Question 79:

  You have created an assembly that utilizes unmanaged code to access external resources. You need to deploy the assembly with the appropriate permissions.

  Which permission set should you use?

  A. SAFE
  B. UNSAFE
  C. EXTERNAL_ACCESS
  D. Default permission set
  B. UNSAFE

• Question 80:

  You are configuring Service Broker to process messages within a single database. You have performed the following steps.

  1.

  CREATE MESSAGE TYPE

  2.

  CREATE CONTRACT

  3.

  CREATE QUEUE

  You need to complete the Service Broker configuration. What should be the next step?

  A. CREATE ROUTE
  B. CREATE SERVICE
  C. CREATE ENDPOINT
  D. CREATE BROKER PRIORITY
  B. CREATE SERVICE