Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 391:

  You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

  You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:

  1. Write the results to a disk.

  2. Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.

  3. The code must NOT use any object delimiters.

  The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use?

  To answer, type the correct code in the answer area.

  Check the answer below

  CREATE TABLE Sales.OrderDetails (

  ListPrice money not null,

  Quantity int not null,

  LineItemTotal as (ListPrice * Quantity) PERSISTED)

• Question 392:

  You administer a Microsoft SQL Server instance that will support several databases.

  You need to ensure that every new database created has a data type named postalcode that contains the same attributes.

  What should you do?

  A. Create a user-defined type on the model database.
  B. Create a user-defined type on the master database.
  C. Create a user-defined data type on the master database.
  D. Create a user-defined data type on the model database.
  D. Create a user-defined data type on the model database.

  ---

  One option is to create SQL Server user defined data types.

  One trick with new databases is to create the objects in the model database, so as new databases are created the user defined data types will automatically be available.

  References: https://www.mssqltips.com/sqlservertip/1628/sql-server-user-defined-data-types-rules-and-defaults/

• Question 393:

  You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks:

  -Joins 8 tables that contain up to 500,000 records each.

  -Performs aggregations on 5 fields.

  -

  The view is frequently used in several reports.

  You need to improve the performance of the reports. What should you do?

  A. Convert the view into a table-valued function.
  B. Convert the view into a Common Table Expression (CTE).
  C. Convert the view into an indexed view.
  D. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.
  C. Convert the view into an indexed view.

  ---

  http://msdn.microsoft.com/en-us/library/ms191432.aspx

• Question 394:

  Which of the following is not a part of an Extended Events package?

  A. Predicates
  B. Targets
  C. Sources
  D. Actions
  C. Sources

  ---

  A package can contain any or all of the following objects :

  -Events

  -Targets

  -Actions

  -Types

  -Predicates

  -Maps

• Question 395:

  You are maintaining a Microsoft SQL Server database that stores order information for an online store website. The database contains a table that is defined by the following Transact-SQL statement.

  

  You need to ensure that purchase order numbers are used only for a single order. What should you do?

  A. Create a new CLUSTERED constraint on the PurchaseOrderNumber column.
  B. Create a new UNIQUE constraint on the PurchaseOrderNumber column.
  C. Create a new PRIMARY constraint on the PurchaseOrderNumber column.
  D. Create a new FOREIGN KEY constraint on the PurchaseOrderNumber column.
  B. Create a new UNIQUE constraint on the PurchaseOrderNumber column.

  ---

  You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key. Reference: UNIQUE Constraints https://technet.microsoft.com/en-us/library/ms191166(v=sql.105).aspx

• Question 396:

  DRAG DROP

  You administer a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Suppliers and an indexed view named VWLocalSuppliers, both of which were created by using the following Transact-SQL statement:

  

  You need to change the data type of the Code column in the Suppliers table to nvarchar(50).

  Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

• Question 397:

  You have an XML schema collection named Sales.InvoiceSchema.

  You need to declare a variable of the XML type named Invoice. The solution must ensure that the Invoice variable is validated against the Sales.InvoiceSchema schema.

  Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-

  SQL segment that has been provided as well as below it.

  

  

  Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  Check the answer below

  DECLARE @Invoice xml (Sales.InvoiceSchema)

  Update line 1.

  You can use a DECLARE statement to create a variable of xml type, as the following example shows.

  DECLARE @x xml

  Further, create a typed xml variable by specifying an XML schema collection, as shown in the following example.

  DECLARE @x xml (Sales.StoreSurveySchemaCollection)

  ---

  https://msdn.microsoft.com/en-us/library/bb522510.aspx

• Question 398:

  You have a database application that uses Microsoft SQL Server 2012. You have a query named Query1 that returns four columns from a frequently updated table that has a clustered index. Three of the columns are referenced in the WHERE clause of the query. The three columns are part of a non-clustered index. The fourth column is not referenced in the WHERE clause.

  Users report that the application begins to run slowly. You determine that the root cause for the performance issue is Query1.

  You need to optimize the statement. What should you do?

  A. Add a HASH hint to the query.
  B. Add a LOOP hint to the query.
  C. Add a FORCESEEK hint to the query.
  D. Add an INCLUDE clause to the index.
  E. Add a FORCESCAN hint to the Attach query.
  F. Add a columnstore index to cover the query.
  G. Enable the optimize for ad hoc workloads option.
  H. Cover the unique clustered index with a columnstore index.
  I. Include a SET FORCEPLAN ON statement before you run the query.
  J. Include a SET STATISTICS PROFILEON statement before you run the query.
  K

  ---

  SET SHOWPLAN_XML (Transact-SQL) causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document

  Incorrect Answers:

  F: Columnstore indexes in the SQL Server Database Engine can be used to significantly speed-up the processing time of common data warehousing queries. Typical data warehousing workloads involve summarizing large amounts of data. But in this question the query is run on a table that is updated frequently, not a warehousing table.

  Reference: SET SHOWPLAN_XML(Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187757.aspx

• Question 399:

  You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected. You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer. What should you do?

  A. Add a HASH hint to the query.
  B. Add a LOOP hint to the query.
  C. Add a FORCESEEK hint to the query.
  D. Add an INCLUDE clause to the index.
  E. Add a FORCESCAN hint to the Attach query.
  F. Add a columnstore index to cover the query.
  G. Enable the optimize for ad hoc workloads option.
  H. Cover the unique clustered index with a columnstore index.
  I. Include a SET FORCEPLAN ON statement before you run the query.
  J. Include a SET STATISTICS PROFILE ON statement before you run the query.
  K

• Question 400:

  Which of the following is not a FLWOR clause?

  A. for
  B. let
  C. where
  D. over
  E. return
  D. over

  ---

  FLWOR (pronounced "flower") is an acronym for

  "For,

  Let,

  Where,

  Order by,

  Return".