Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 31:

  Which of the following datatypes has a fixed precision and a scale of six digits?

  A. Double
  B. Money
  C. Int
  D. Numeric
  E. SmallInt
  F. VarInt
  G. Float
  D. Numeric

• Question 32:

  You need to build a table structure for a stored procedure to support data entry from a website form. The website form must meet the following requirements:

  Users must validate their age as 18 or older to use the website.

  Users who leave the date of birth field blank, or who enter an invalid date, must receive an error message.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Add the CHECK constraint to the table structure.
  B. Add the DATEPART function to the stored procedure.
  C. Add the DEFAULT constraint to the table structure.
  D. Add the SYSDATETIMEOFFSET function to the stored procedure.
  E. Add the ISDATE function to the stored procedure.
  D. Add the SYSDATETIMEOFFSET function to the stored procedure.
  E. Add the ISDATE function to the stored procedure.

  ---

  SYSDATETIMEOFFSET returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. ISDATE returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. References:https://msdn.microsoft.com/en-us/library/bb677334.aspx

• Question 33:

  When referring in the OUTPUT clause to columns from the inserted rows, when should you prefix the columns with the keyword inserted?

  A. Always
  B. Never
  C. Only when the statement is UPDATE
  D. Only when the statement is MERGE
  A. Always

• Question 34:

  DRAG DROP

  A database has a Customers table that includes the following fields:

  

  You must add a new field named AccountBalance. The AccountBalance field has the following requirements:

  1. The account balance must always include a value.

  2. The account balance must have an initial value of 0.

  3. The account balance must not have a negative value.

  You need to add the AccountBalance fields to the table.

  How should you complete the relevant Transact-SQL statement? To answer, drag the appropriate Transact-SQL segment to the correct location or location. Each TRANSACT-SQL segment may be used once, more than once, or not at all.

  You may need to drag the split bar between panes or scroll to view content.

  Select and Place:

  

  

• Question 35:

  DRAG DROP

  You are the administrator for a heavily-used OLTP Microsoft SQL Server database.

  You are troubleshooting performance issues seen when using stored procedures in the database. The database stores millions of orders across thousands of customers. Some of the customers have large numbers of orders, while others

  have only one order. You update the statistics and perform defragmentation of all tables and indexes, but two stored procedures still have issues when accessing data.

  p_GetCustomer accepts @companyID as a parameter. From the results of profiling, you know that 90 percent of the calls use the @companyid value of 5, while the other 10 percent of calls are evenly distributed across another 10000 values.

  While viewing the execution plan, you discover that a non-clustered index seek is used.

  p_GetShipDate accepts @orderID as a parameter and returns the ship date for that order. You discover that the execution plan is performing a scan on a non-clustered index that has orderID as the index key.

  You need to add appropriate query hints to each stored procedure to improve the performance.

  What should you do? To answer, drag the appropriate procedures to the correct hints. Each procedure may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

  Box 1: Optimize FOR..

  OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )

  Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.

  Box 2: FORCESEEK

  FORCESEEK [ (index_value(index_column_name [ ,... n ] )) ]

  Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

  https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017

• Question 36:

  DRAG DROP

  You administer a Microsoft SQL Server database.

  A vendor provides an encrypted stored procedure named MyProcedure that produces the following result set:

  

  You want to produce a result set that includes the following columns with the data types indicated:

  

  You only have permissions to execute MyProcedure.

  You need to ensure that the result set has the required data types.

  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:

  

  

  ---

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver15

• Question 37:

  You have a data warehouse that contains the data for all the customers of your company.

  You need to create a query dynamically generates a SELECT statement from a table named CUSTOMERS. The SELECT statement must generate a full list of columns.

  Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code 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.

  XML PATH

  In line 7 add XML PATH to get thefollowing line:

  FOR XML PATH (' ')), 1, 1, ' ') +

  Here is how it works:

  1. Get XML element string with FOR XML Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained inthe PATH argument. For example, if we were to run the following statement: SELECT ',' + name

  FROM temp1

  FOR XML PATH ('')

  By passing in a blank string (FOR XML PATH('')), we get the following instead:

  ,aaa,bbb,ccc,ddd,eee

  2. Remove leading commawith STUFF

  The STUFF statement literally "stuffs” one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.

  SELECT abc = STUFF( (

  SELECT ',' +NAME

  FROM temp1

  FOR XML PATH('')

  ), 1, 1, '')

  FROM temp1

  Note: The full code will be:

  SELECT 'SELECT' +

  STUFF ((

  SELECT ', [' + name + ']'

  FROM

  WHERE id = OBJECT_ID('Customers') AND

  …name <> 'me'

  FOR XML PATH (' ')), 1, 1, ' ') +

  'FROM[Customers] '

  ---

  http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-pathwork-in-sql-server

• Question 38:

  As part of a new enterprise project, you're designing a new table to store financial transactions. This table could eventually store millions of rows and so storage space is very important. One of the columns in the table will store either a 1 or 0 value. Which data type would be most appropriate?

  A. bit
  B. tinyint
  C. numeric
  D. float
  A. bit

• Question 39:

  You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns. Id is the Primary Key. You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. Which Transact-SQL statement should you use?

  

  A. UPDATE TOP(10) BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0)
  B. UPDATE BlogEntry SET Summary = CAST(N' This is in a draft stage' as nvarchar(max)) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
  C. UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM ( SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID
  D. UPDATE BlogEntrySET Summary.WRITE(N' This is in a draft stage', 0, 0) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
  C. UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM ( SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID

  ---

  According to the append possibility of .Write method when offset is specified as NULL. https://msdn.microsoft.com/en-us/library/ms177523.aspx

• Question 40:

  Which of the following are true about synonyms? (Choose all that apply.)

  A. Synonyms do not store T-SQL code or data.
  B. Synonyms do not require schema names.
  C. Synonym names can match those of the objects they refer to.
  D. Synonyms can reference objects in other databases or through linked servers.
  A. Synonyms do not store T-SQL code or data.
  D. Synonyms can reference objects in other databases or through linked servers.