Download Querying Microsoft SQL Server 2012-2014.70-461.PracticeTest.2018-07-05.111q.tqb

Vendor:	Microsoft
Exam Code:	70-461
Exam Name:	Querying Microsoft SQL Server 2012/2014
Date:	Jul 05, 2018
File Size:	8 MB

Download Exam

How to open VCEX files?

Files with VCEX extension can be opened by ProfExam Simulator.

Download
ProfExam Simulator

Purchase
Coupon: EXAM_HUB

Discount: 20%

Demo Questions

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/

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

You use a Microsoft SQL Server 2012 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