Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 191:

  You are creating a table that stores the GPS location of customers. You need to ensure that the table allows you to identify customers within a specified sales boundary and to calculate the distance between a customer and the nearest store.

  Which data type should you use?

  A. geometry
  B. geography
  C. nvarchar(max)
  D. varbinary(max) FILESTREAM
  B. geography

  ---

  The GEOGRAPHY data type is used to store ellipsoidal (round-earth) data. It is used to store latitude and longitude coordinates that represent points, lines, and polygons on the earth's surface. For example, GPS data that represents the lay of the land is one example of data that can be stored in the GEOGRAPHY data type.

• Question 192:

  You are writing a query that returns a list of products that have grossed more than $10,000.00 during the year 2007. You need to insert the following filter expression into the query. SUM([Order Details].UnitPrice * [Order Details].Quantity) > 10000 Into which clause should you insert this expression?

  A. ON
  B. WHERE
  C. HAVING
  D. GROUP BY
  C. HAVING

• Question 193:

  You are tasked to analyze blocking behavior of the following query:

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

  WITH Customers AS (

  SELECT *

  FROM Customer ),

  SalesTotal AS (

  SELECT CustomerId, SUM(OrderTotal) AS AllOrderTotal FROM SalesOrder)

  SELECT CustomerId, AllOrderTotal

  FROM SalesTotal

  WHERE AllOrderTotal > 10000.00;

  You need to determine if other queries that are using the Customer table will be blocked by this query. You also need to determine if this query will be blocked by other queries that are using the Customer table.

  What behavior should you expect?

  A. The other queries will be blocked by this query. This query will be blocked by the other queries.
  B. The other queries will be blocked by this query. This query will not be blocked by the other queries.
  C. The other queries will not be blocked by this query. This query will be blocked by the other queries.
  D. The other queries will not be blocked by this query. This query will not be blocked by the other queries.
  D. The other queries will not be blocked by this query. This query will not be blocked by the other queries.

• Question 194:

  You are a developer for a Microsoft SQL Server 2008 R2 database instance used to support a customer service application. You create tables named complaint, customer, and product as follows:

  

  You need to write a query to sum the sales made to each customer who has made a complaint by the following entries: Each customer name Each product name The grand total of all sales

  Which SQL query 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
  B. Option B

• Question 195:

  You need to build CREATE INDEX statements for all the missing indexes that SQL Server has identified.

  Which dynamic management view should you use?

  A. sys.dm_db_index_usage_stats
  B. sys.dm_db_missing_index_details
  C. sys.dm_db_missing_index_columns
  D. sys.dm_db_missing_index_group_stats
  B. sys.dm_db_missing_index_details

  ---

  sys.dm_db_missing_index_details returns detailed information about missing indexes, excluding spatial indexes.

• Question 196:

  You are using Database Mail to deliver email notification and are notified that an employee has not been receiving emails.

  You need to determine if any email notifications sent by Database Mail have been unsuccessful.

  Which object from the msdb database should you use?

  A. msdb.dbo.sysmail_event_log
  B. msdb.dbo.sysmail_sentitems
  C. msdb.dbo.sysmail_unsentitems
  D. msdb.dbo.sysmail_faileditems
  D. msdb.dbo.sysmail_faileditems

  ---

  sysmail_faileditems

  Contains one row for each Database Mail message with the failed status. Use this view to determine which messages were not successfully sent.

• Question 197:

  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 the schema, you should confirm that it is no longer in use. You need to use a catalog view to determine if the schema collection is being used.

  Which catalog view should you use?

  A. sys.xml_schema_components
  B. sys.xml_schema_namespaces
  C. sys.xml_schema_collections
  D. sys.column_xml_schema_collection_usages
  D. sys.column_xml_schema_collection_usages

  ---

  sys.column_xml_schema_collection_usages returns a row for each column that is validated by an XML schema.

• Question 198:

  You need to identify which tables are referenced by name in a stored procedure that does not use dynamic SQL.

  Which catalog view should you use?

  A. sys.procedures
  B. INFORMATION_SCHEMA.TABLES
  C. INFORMATION_SCHEMA.ROUTINES
  D. sys.sql_expression_dependencies
  D. sys.sql_expression_dependencies

• Question 199:

  You administer a Microsoft SQL Server 2008 instance that has two databases. The first database named AdventureWorks contains a table named Sales.SalesOrders. The Sales.SalesOrders table has the following definition:

  

  The second database named AdventureWorksDW contains a table named dbo.SalesOrderSummary. The dbo.SalesOrderSummary table has the following definition:

  

  You plan to migrate sales data for the year 2011 from the SalesOrderDetail table into the SalesOrderSummary table. You need to ensure that the following requirements are met:

  All data is removed from the SalesOrderSummary table before migrating data. A subset of data is migrated from the SalesOrderDetail table in the AdventureWorks database to the SalesOrderSummary table in the AdventureWorksDW database.

  Migrated data summarizes order quantity in one row per product for the year 2011.

  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 200:

  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 have permission to perform this action." You need to have the least amount of permissions granted to access the dynamic management views.

  Which permissions should be granted?

  A. CONTROL
  B. VIEW SERVER STATE
  C. VIEW DATABASE STATE
  D. CREATE EXTERNAL ACCESS ASSEMBLY
  B. VIEW SERVER STATE