70-461 PDF

How to use our free PDF

Our Free PDF is based on the full mock exams which is available on our Web Site. The PDF consists in an extract of questions and answers with detailed explanations that are available to be:

Q1.Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tablescontain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The datain the two tables is distinct from one another. Business users want a report that includes aggregateinformation about the total number of global sales and total sales amounts. You need to ensure that yourquery executes in the minimum possible time. Which query should you use?
A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrdersUNION ALLSELECT SalesOrderId, SalesAmountFROM InternationalSalesOrders) AS p
B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM (SELECT SalesOrderId, SalesAmountFROM DomesticSalesOrdersUNIONSELECT SalesOrderId, SalesAmountFROM InternationalSalesOrders) AS p
C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrdersUNIONSELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM InternationalSalesOrders
D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
FROM DomesticSalesOrdersUNION ALLSELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmountFROM InternationalSalesOrders
Option A is correct.
Explanation:
http://msdn.microsoft.com/en-us/library/ms180026.aspx http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimalperformance-comparison/

Q2.You create a stored procedure that will update multiple tables within a transaction. You need to ensure thatif the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. WhichTransact-SQL statement should you include at the beginning of the stored procedure?
A. SET XACT_ABORT ON

B. SET ARITHABORT ON

C. TRY

D. BEGIN

E. SET ARITHABORT OFF

F. SET XACT_ABORT OFF

Option A is correct.
Explanation:
http://msdn.microsoft.com/en-us/library/ms190306.aspx http://msdn.microsoft.com/en-us/library/ms188792.aspx

Q3.You are a database developer at an independent software vendor. You create stored procedures thatcontain proprietary code. You need to protect the code from being viewed by your customers. Which storedprocedure option should you use?
A. ENCRYPTBYKEY

B. ENCRYPTION

C. ENCRYPTBYPASSPHRASE

D. ENCRYPTBYCERT

Option B is correct.
Explanation:
http://technet.microsoft.com/en-us/library/bb510663.aspx http://technet.microsoft.com/en-us/library/ms174361.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx http://technet.microsoft.com/en-us/library/ms190357.aspx http://technet.microsoft.com/en-us/library/ms188061.aspx

Q4.You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Worddocuments. You need to ensure that the documents must only be accessible via Transact-SQL queries.Which Transact-SQL statement should you use?
A. CREATE TABLE DocumentStore
([Id] INT NOT NULL PRIMARY KEY,[Document] VARBINARY(MAX) NULL)GO
B. CREATE TABLE DocumentStore
([Id] hierarchyid,[Document] NVARCHAR NOT NULL)GO
C. CREATE TABLE DocumentStore AS FileTable

D. CREATE TABLE DocumentStore
([Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,[Document] VARBINARY(MAX) FILESTREAM NULL)GO
Option A is correct.
Explanation:
http://msdn.microsoft.com/en-us/library/gg471497.aspx http://msdn.microsoft.com/en-us/library/ff929144.aspx

Q5.You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. Youdiscover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reducefragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batchshould you use?
A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING

B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

C. ALTER INDEX ALL ON OrderDetail REBUILD

D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Option B is correct.
Explanation:
http://msdn.microsoft.com/en-us/library/ms188388.aspx

Q6.You develop a Microsoft SQL Server 2012 database. The database is used by two web applications thataccess a table named Products. You want to create an object that will prevent the applications fromaccessing the table directly while still providing access to the required data. You need to ensure that thefollowing requirements are met:Future modifications to the table definition will not affect the applications' ability to access data.The new object can accommodate data retrieval and data modification.You need to achieve this goal by using the minimum amount of changes to the existing applications. Whatshould you create for each application?
A. views

B. table partitions

C. table-valued functions

D. stored procedures

Option A is correct.
Explanation:


Q7.You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets thefollowing requirements:Returns a result set based on supplied parameters. Enables the returned result set to perform a join with a table.Which object should you use?
A. Inline user-defined function

B. Stored procedure

C. Table-valued user-defined function

D. Scalar user-defined function

Option C is correct.
Explanation:


Q8.You develop a Microsoft SQL Server 2012 database. You need to create and call a stored procedure thatmeets the following requirements:Accepts a single input parameter for CustomerID.Returns a single integer to the calling application.Which Transact-SQL statement or statements should you use? (Each correct answer presents part of thesolution. Choose all that apply.)
A. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,@CustomerRating INT OUTPUTASSET NOCOUNT ONSELECT @CustomerRating = CustomerOrders/CustomerValueFROM CustomersWHERE CustomerID = @CustomerIDRETURNGO
B. EXECUTE dbo.GetCustomerRating 1745

C. DECLARE @CustomerRatingByCustomer INT
DECLARE @Result INTEXECUTE @Result = dbo.GetCustomerRating1745,@CustomerRatingByCustomer
D. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,@CustomerRating INT OUTPUTASSET NOCOUNT ONSELECT @Result = CustomerOrders/CustomerValueFROM CustomersWHERE CustomerID = @CustomerIDRETURN @ResultGO
E. DECLARE @CustomerRatingByCustomer INT
EXECUTE [email protected] = 1745,@CustomerRating = @CustomerRatingByCustomer OUTPUT
F. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INTASDECLARE @Result INTSET NOCOUNT ONSELECT @Result = CustomerOrders/CustomerValueFROM CustomersWHERE CustomerID = @CustomerIDRETURNS @ResultGO
Option A,E are correct.
Explanation:


Q9.You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical. Youwrite the following Transact-SQL query:INSERT INTO OrdersHistoricalSELECT * FROM CompletedOrdersYou need to optimize transaction logging and locking for the statement. Which table hint should you use?
A. HOLDLOCK

B. ROWLOCK

C. XLOCK

D. UPDLOCK

E. TABLOCK

Option E is correct.
Explanation:
http://technet.microsoft.com/en-us/library/ms189857.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx

Q10.You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader andSalesOrderDetail. The indexes on the tables are as shown in the exhibit.
[PIC-1]
You write the following Transact-SQL query:
[PIC-2]
You discover that the performance of the query is slow. Analysis of the query plan shows table scans wherethe estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index onSalesOrderDetail. You need to improve the performance of the query. What should you do?
A. Use a FORCESCAN hint in the query.

B. Add a clustered index on SalesOrderId in SalesOrderHeader.

C. Use a FORCESEEK hint in the query.

D. Update statistics on SalesOrderId on both tables.

Option D is correct.
Explanation:
http://msdn.microsoft.com/en-us/library/ms187348.aspx