Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 91:

  You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales amount for each of ABC.com's 20 sales representatives.

  You need to write a Transact-SQL query that ranks the sales representatives by the average sales amount for the past year. You want the sales representatives with the same average sales amount to have the same rank with the subsequent rank being skipped.

  Which ranking function should you use?

  A. The RANK( ) OVER function.
  B. The NTILE( ) OVER function
  C. The DENSE_RANK( ) OVER function
  D. The ROW_NUMBER( ) OVER function
  E. The FORMAT function.
  A. The RANK( ) OVER function.

  ---

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

• Question 92:

  You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database infrastructure with a very large database named SalesDB.

  You create a new table named SalesHistory that will hold historical data from the SalesDB database.

  You need to perform a batch update from the SalesDB database to the SalesHistory table. You want the status information from the batch process to be logged to a SQL Server table that must be created by the batch process.

  How would you accomplish this task?

  A. You should make use of the FORMAT function.
  B. You should make use of the CONVERT function.
  C. You should make use of a scalar user-defined function.
  D. You should make use of an inline function.
  E. You should make use of a table-valued function.
  F. You should make use of a stored procedure.
  F. You should make use of a stored procedure.

• Question 93:

  You develop a Microsoft SQL Server database that contains a two tables named OrdersHistorical and CompletedOrders. Both tables have the same schema that has the following definition:

  

  You write the following Transact-SQL statement:

  

  You need to ensure that data that falls into the date range of the SELECT statement cannot be inserted into CompletedOrders while the INSERT statement is running. You also need to ensure that other operations are unaffected.

  Which table hint should you use?

  A. ROWLOCK
  B. HOLDLOCK
  C. XLOCK
  D. TABLOCK
  E. UPDLOCK
  E. UPDLOCK

  ---

  UPDLOCK s

  pecifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level.

  Incorrect Answres:

  Not A: ROWLOCK specifies that row locks are taken when page or table locks are ordinarily taken.

  B: HOLDLOCK is equivalent to SERIALIZABLE. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in.

  Note: SERIALIZABLE is equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not

  D: TABLOCK specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK,

  the shared lock is applied to the entire table instead of at the row or page level.

  When importing data into a heap by using the INSERT INTO SELECT FROM statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the

  target table

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

• Question 94:

  DRAG DROP

  You are developing a Microsoft SQL Server database.

  You need to meet the following requirements:

  1. Create a table that contains a primary key column and an XML data column.

  2. Prevent insertion of invalid XML.

  3. Load an XML document.

  Which three 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:

  

  

  ---

  Explanation:

  Box 1: CREATE XML SCHEMA COLLECTION MySchema AS

  CREATE XML SCHEMA COLLECTION imports the schema components into a database.

  Box 2: CREATE TABLE .. xmlColumn xml(MySchema)

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

  DECLARE @x xml (Sales.StoreSurveySchemaCollection)

  Box 3: LoadXml()

  LoadXml() loads the XML document from the specified string.

  Reference:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-xml-schema-collection-transact-sql

  https://docs.microsoft.com/en-us/sql/relational-databases/xml/create-xml-data-type-variables-and-columns

• Question 95:

  You are the database administrator of a SQL Server 2012 database infrastructure at ABC.com.

  You need to optimize a very large database table that contains several million rows of data by designing a view based on the table. The view must allow users to perform aggregations on several columns.

  How should you configure the view to ensure optimal performance?

  A. You should create the view as an indexed view.
  B. You should create a clustered index on the view.
  C. You should make use of a stored procedure to return that data.
  D. You should make use of a table-valued function.
  A. You should create the view as an indexed view.

• Question 96:

  You are designing a table for a SQL Server database. The table uses horizontal partitioning.

  You have the following requirements:

  Each record in the table requires a unique key.

  You must minimize table fragmentation as the table grows.

  You need to choose the appropriate data type for the key value.

  What should you do?

  A. Use the NEWID function to create a unique identifier.
  B. Use the NEWSEQUENTIALID function to create a unique identifier.
  C. Generate a random value that uses the bigint datatype.
  D. Generate a random value that uses the char(16) data type.
  B. Use the NEWSEQUENTIALID function to create a unique identifier.

  ---

  Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference the appropriate table.

  NEWSEQUENTIALID creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

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

• Question 97:

  What is the limit for the number of steps in statistic histograms?

  A. 10 steps per histogram
  B. 200 histograms per column
  C. 200 pages per histogram
  D. 200 steps per histogram
  D. 200 steps per histogram

• Question 98:

  You generate a daily report according to the following query:

  

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

  

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

• Question 99:

  You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.

  

  Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the

  Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.

  On which column in the Employee table should you create a Primary Key constraint for this table?

  A. DateHired
  B. DepartmentID
  C. EmployeeID
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  C. EmployeeID

• Question 100:

  You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data.

  BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book.

  You need to ensure that the new column is assigned a GUID for existing rows.

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

  Select and Place:

  

  ---

  

  Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.