Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 51:

  You work as a SQL Server 2012 database developer at ABC.com.

  You are developing a database that will be used by a Web application. The database will store small multimedia files in several tables. The largest multimedia file is 975 kB. These multimedia files will be retrieved quite often.

  How would you store these files?

  A. You should make use of a cursor.
  B. You should make use of a row-level compression.
  C. You should make use of the FileStream data type.
  D. You should make use of the VARBINARY data type.
  D. You should make use of the VARBINARY data type.

• Question 52:

  What is the purpose of the GROUPING and GROUPING_ID functions? (Choose all that apply.)

  A. You can use these functions in the GROUP BY clause to group data.
  B. You can use these functions to tell whether a NULL in the result represents a placeholder for an element that is not part of the grouping set or an original NULL from the table.
  C. You can use these functions to uniquely identify the grouping set that the result row is associated with.
  D. These functions can be used to sort data based on grouping set association that is, first detail, and then aggregates.
  B. You can use these functions to tell whether a NULL in the result represents a placeholder for an element that is not part of the grouping set or an original NULL from the table.
  C. You can use these functions to uniquely identify the grouping set that the result row is associated with.
  D. These functions can be used to sort data based on grouping set association that is, first detail, and then aggregates.

• Question 53:

  DRAG DROP

  You use Microsoft SQL Server to develop a database.

  You need to create a query that displays a list of all customers and related orders. The query must display customers that have no related orders. If the last order for a customer was within the last 30 days, the query must return the most

  recent OrderDate for the customer.

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

  Select and Place:

  

  

• Question 54:

  You use Microsoft SQL Server 2012 to develop a database application.

  Your application sends data to an NVARCHAR(MAX) variable named @var.

  You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9).

  Which code segment should you use?

  A. BEGIN TRY SELECT convert (decimal(36,9), @var) as Value, 'True' As BadCast END TRY BEGIN CATCH SELECT convert (decimal(36,9), @var) as Value, 'False' As BadCast END CATCH
  B. TRY( SELECT convert (decimal(36,9), @var) SELECT 'True' As BadCast ) CATCH( SELECT 'False' As BadCast )
  C. SELECT CASE WHEN convert (decimal(36,9), @var) IS NULL THEN 'True' ELSE 'False' END AS BadCast
  D. SELECT IF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False' ) AS BadCast
  D. SELECT IF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False' ) AS BadCast

  ---

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

• Question 55:

  You need to design a table for a customer management application that will store a list of country names. The table must meet the following requirements:

  1.

  Country names must be stored in English only.

  2.

  The disk space used to store the table must be minimized.

  Which table design should you use?

  

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

  ---

  Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use. Regarding memory usage, nvarchar uses 2 bytes per character, whereas varchar uses 1.

  Skip the use of a CountryID column to save space.

  References: https://dba.stackexchange.com/questions/36081/write-differences-between-varchar-and-nvarchar

• Question 56:

  What is not a type of plan guide?

  A. JOIN
  B. TEMPLATE
  C. SQL
  D. OBJECT
  A. JOIN

• Question 57:

  DRAG DROP

  You have a Microsoft SQL Server database. The database contains a group of tables created by using the following Transact-SQL statements:

  

  You need to write a query that returns all the salesperson's phone numbers and an indicator as to whether you can contact the salesperson using that number. The indicator must meet the following requirements:

  1. If the phone number is a work phone number, the indicator should show the value 'Allow Calls'

  2. If the phone number is NULL, the indicator should show the value 'Unrecognized Phone Type'

  3. In all other cases, the indicator should show the value 'Do Not Call'

  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:

  

  

  ---

  FROM COALESCE IIF

  Box 1: SELECT

  Box 2: COALESCE

  COALESCE evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL.

  Box 3: FROM -

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

• Question 58:

  How does SET XACT_ABORT ON affect a transaction?

  A. If a T-SQL error with a severity level > 16 occurs, the transaction will be aborted.
  B. If a T-SQL error with a severity level > 10 occurs, the transaction will be aborted.
  C. If a T-SQL error with a severity level > 16 occurs, some statements of the transaction may still be executed.
  D. If a T-SQL error with a severity level > 10 occurs, some statements of the transaction may still be executed.
  B. If a T-SQL error with a severity level > 10 occurs, the transaction will be aborted.

  ---

  severity level :

  Severity level 0-10: These are just information message not actual error.

  Severity level 11 to 16: These are errors caused due to user mistakes. We have tried to divide value by 0 in previous article and hence we got severity error 16.

  Severity Level 17: This severity indicates that an operation making SQL Server out of resources or exceeding defined limit. That may be disk space or lock limit.

  Severity Level 18: This error represents nonfatal internal software error.

  Severity Level 19: This error represents some non-configurable internal limit has been exceeded and the current batch process is terminated. To be very frank, I have not seen this severity practically in my life.

  Severity Level 20: This severity indicates current statement has encountered a problem and because of this severity level client connection with SQL Server will be disconnected.

  Severity Level 21: This severity indicates that you have encountered a problem that affects all processes in the current database.

  Severity Level 22: This error indicates problem with database table or index. It may be corrupt or damaged.

  Severity Level 23: This error indicates problem with database integrity which may be fixed by DBCC command.

  Severity Level 24: This error indicates problem with the hardware of SQL Server. Need to check disk drive and related hardware extensively.

• Question 59:

  You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a large database

  New rows are inserted into the tables in the SalesDB database and updates to existing rows occur on a high frequency. The inserts and updates often blocked by queries retrieving and reading data.

  How would you prevent queries that retrieve and read data from blocking queries that insert and update data?

  A. You should make use of the SERIALIZABLE ISOLATION LEVEL.
  B. You should make use of the SNAPSHOT ISOLATION LEVEL.
  C. You should make use of the REPEATABLE READ ISOLATION LEVEL.
  D. You should make use of the READCOMMITTED ISOLATION LEVEL.
  E. You should make use of the READPAST ISOLATION LEVEL
  B. You should make use of the SNAPSHOT ISOLATION LEVEL.

  ---

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

• Question 60:

  Which DMO gives you information about index usage?

  A. sys.dm_exec_query_stats
  B. sys.dm_exec_query_text
  C. sys.dm_db_index_usage_stats
  D. sys.indexes
  C. sys.dm_db_index_usage_stats