2018 70-461 Real Exam Questions - Microsoft 70-461 Real Braindumps Dumps4Download.us

Question No : 1

Drag and Drop Question
You develop an SQL Server database. The database contains a table that is defined by the following T-SQL statements:

The table contains duplicate records based on the combination of values in the surName, givenName, and dateOfBirth fields.
You need to remove the duplicate records.
How should you complete the relevant Transact-SQL statements? To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.
Each code 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.

Answer: 

Explanation:
Example:
let us write a query which will delete all duplicate data in one shot. We will use a CTE (Common Table Expression) for this purpose. We will read in future posts what a CTE is and why it is used.
On a lighter note, CTE's can be imagined as equivalent to temporary result sets that can be used only in an underlying SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.
;WITH CTE AS
(
SELECT Name
, City
, [State]
, ROW_NUMBER() OVER(PARTITION BY Name, City, [State] ORDER BY [Name]) AS Rnum
FROM Persons
)
DELETE FROM CTE WHERE Rnum <> 1
In the code by saying WHERE Rnum <> 1, we are asking SQL Server to keep all the records with Rank 1, which are not duplicates, and delete any other record. After executing this query in SQL Server Management Studio, you will end up with no duplicates in your table. To confirm that just run a simple query against your table.

Reference: 
How to Remove Duplicates from a Table in SQL Server
http://social.technet.microsoft.com/wiki/contents/articles/22706.how-to-remove-duplicates-from-atable-in-sql-server.aspx

Question No : 2
You are maintaining a Microsoft SQL Server database. You run the following query

You observe performance issues when you run the query. 
You capture the following query execution plan:

You need to ensure that the query performs returns the results as quickly as possible. Which action should you perform?

A. Add a new index to the ID column of the Person table.
B. Add a new index to the EndDate column of the History table.
C. Create a materialized view that is based on joining data from the ActiveEmployee and History tables.
D. Create a computed column that concatenates the GivenName and SurName columns.

Answer: A
Explanation:
Cost is 53% for the Table Scan on the Person (p) table. This table scan is on the ID column, so we should put an index on it.

Buy Microsoft 70-461 Real Exam Dumps - 2018 70-461 Braindumps Dumps4Download

Question No : 3

You are developing a Microsoft SQL Server 2012 database for a company. The database contains a table that is defined by the following Transact-SQL statement:

You use the following Transact-SQL script to insert new employee data into the table. Line numbers are included for reference only. 

If an error occurs, you must report the error message and line number at which the error occurred and continue processing errors. You need to complete the Transact-SQL script. Which Transact-SQL segment should you insert at line 06?

A. SELECT ERROR_LINE(), ERROR_MESSAGE()

B. DECLARE @message NVARCHAR(1000),@severity INT, @state INT; SELECT @message =
ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE();
RAISERROR (@message, @severity, @state);

C. DECLARE @message NVARCHAR(1000),@severity INT, @state INT; SELECT @message =
ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE();
THROW (@message, @severity, @state);

D. THROW;

Answer: B
Explanation:
When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling 
application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR
and PRINT statements.

Reference: 
TRY...CATCH (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms175976.aspx

Buy Exact 70-461 Exam Questions With Answers - 70-461 Dumps PDF Dumps4Download

Question No : 4

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
- Takes an input parameter
- Returns a table of values
- Can be referenced within a view

Which object should you use?

A. inline table-valued function
B. user-defined data type
C. stored procedure
D. scalar-valued function

Answer: A
Explanation:
Incorrect answers:
Not B: A user-defined data type would not be able to take an input parameter.
Not C: A stored procedure cannot be used within a view.

Not D: A scalar-valued would only be able to return a single simple value, not a table.

Question No : 5

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory.Sales.fn_OrdersByTerritory must meet the following requirements:

- Use one-part names to reference columns.
- Return the columns in the same order as the order used in
OrdersByTerritoryView.
- Part of the correct T-SQL statement has been provided in the answer area.
Provide the complete code.

Answer:
CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int)
RETURNS TABLE
AS 
RETURN
(
SELECT
OrderID,
OrderDate,
SalesTerritoryID,
TotalDue
FROM Sales.OrdersByTerritory
WHERE SalesTerritoryID=@T
)

Microsoft 70-461 Exam Study Guide - 70-461 Question Answers Dumps4Download.us