Free Download 70-461 Examp Dump: Microsoft.70-461.PracticeTest.2018-10-11.124q.tqb

File Info

Exam	Querying Microsoft SQL Server 2012/2014
Number	70-461
File Name	Microsoft.70-461.PracticeTest.2018-10-11.124q.tqb
Size	10 MB
Posted	Oct 11, 2018
Download	Microsoft.70-461.PracticeTest.2018-10-11.124q.tqb

How to open VCEX & EXAM Files?

Files with VCEX & EXAM extensions can be opened by ProfExam Simulator.

Download
ProfExam Simulator

Purchase

Coupon: MASTEREXAM
With discount: 20%

Demo Questions

Question 1

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another.

Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.

You need to ensure that your query executes in the minimum possible time.

Which query should you use?

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM (
    SELECT SalesOrderId, SalesAmount
    FROM DomesticSalesOrders
    UNION ALL
    SELECT SalesOrderId, SalesAmount
    FROM InternationalSalesOrders
  ) AS p
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (
    SELECT SalesOrderId, SalesAmount
    FROM DomesticSalesOrders
    UNION
    SELECT SalesOrderId, SalesAmount
    FROM InternationalSalesOrders
  ) AS p
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
  UNION
  SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
  FROM InternationalSalesOrders
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrders
  UNION ALL
  SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
  FROM InternationalSalesOrders

Correct answer: A

Explanation:

Reference: http://msdn.microsoft.com/en-us/library/ms180026.aspxReference: http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimalperformance-comparison/

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

Reference: http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimalperformance-comparison/

Question 2

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code.

You need to protect the code from being viewed by your customers.

Which stored procedure option should you use?

ENCRYPTBYKEY
ENCRYPTION
ENCRYPTBYPASSPHRASE
ENCRYPTBYCERT

Correct answer: B

Explanation:

Reference: http://technet.microsoft.com/en-us/library/bb510663.aspxReference: http://technet.microsoft.com/en-us/library/ms174361.aspxReference: http://msdn.microsoft.com/en-us/library/ms187926.aspxReference: http://technet.microsoft.com/en-us/library/ms190357.aspxReference: http://technet.microsoft.com/en-us/library/ms188061.aspx

Reference: http://technet.microsoft.com/en-us/library/bb510663.aspx

Reference: http://technet.microsoft.com/en-us/library/ms174361.aspx

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

Reference: http://technet.microsoft.com/en-us/library/ms190357.aspx

Reference: http://technet.microsoft.com/en-us/library/ms188061.aspx

Question 3

You use a Microsoft SQL Server database.

You want to create a table to store Microsoft Word documents.

You need to ensure that the documents must only be accessible via Transact-SQL queries.

Which Transact-SQL statement should you use?

CREATE TABLE DocumentStore
(
[Id] INT NOT NULL PRIMARY KEY,
[Document] VARBINARY(MAX) NULL
)
GO
CREATE TABLE DocumentStore
(
[Id] hierarchyid,
[Document] NVARCHAR NOT NULL
)
GO
CREATE TABLE DocumentStore AS FileTable
CREATE TABLE DocumentStore
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[Document] VARBINARY(MAX) FILESTREAM NULL
)
GO