Act now and download your Microsoft 70-461 test today! Do not waste time for the worthless Microsoft 70-461 tutorials. Download Update Microsoft Querying Microsoft SQL Server 2012 exam with real questions and answers and begin to learn Microsoft 70-461 with a classic professional.

2021 Feb 70-461 exam answers

Q61. DRAG DROP 

You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition: 

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use? 

(To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) 

Answer: 


Q62. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. 

The business team wants a report that displays the total number of orders placed on the current day. 

You need to write a query that will return the correct results in the most efficient manner. 

Which Transact-SQL query should you use? 

A. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime = CONVERT(DATE, GETDATE()) 

B. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime = GETDATE() 

C. SELECT COUNT(*) FROM SalesOrders 

WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 

112)) 

D. SELECT COUNT(*) FROM SalesOrders 

WHERE OrderTime >= CONVERT(DATE, GETDATE()) 

AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) 

Answer: D 


Q63. You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. 

You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. 

You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. 

Which Transact-SQL statement should you use? 

A. USE Database2 

B. EXECUTE AS OWNER 

C. USE Database1 

D. EXECUTE AS CALLER 

Answer: B 


Q64. 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? 

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 

Answer: A 


Q65. Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD parameter. 

You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence. 

Which approach should you use? 

A. A SELECT statement that includes CASE 

B. Cursor 

C. BULK INSERT 

D. View 

E. A user-defined function 

Answer: E 

Explanation: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. 


Replace 70-461 free draindumps:

Q66. CORRECT TEXT 

You need to create a query that calculates the total sales of each OrderlD from a table named Sales.Details. The table contains two columns named OrderlD and ExtendedAmount. 

The solution must meet the following requirements: 

Use one-part names to reference columns. 

Start the order of the results from OrderlD. 

NOT depend on the default schema of a user. 

Use an alias of TotalSales for the calculated ExtendedAmount. 

Display only the OrderlD column and the calculated TotalSales column. 

Provide the correct code in the answer area. 

Answer: 


Q67. You are writing a set of queries against a FILESTREAM-enabled database. 

You create a stored procedure that will update multiple tables within a transaction. 

You need to ensure that if the stored procedure raises a runtime error, the entire 

transaction is terminated and rolled back. 

Which Transact-SQL statement should you include at the beginning of the stored 

procedure? 

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

B. SET XACT_ABORT OFF 

C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

D. SET IMPLICIT_TRANSACTIONS ON 

E. SET XACT_ABORT ON 

F. SET IMPLICIT TRANSACTIONS OFF 

Answer: E 


Q68. You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. 

You need to ensure that users are able to modify data by using the view. 

What should you do? 

A. Create an AFTER trigger on the view. 

B. Modify the view to use the WITH VIEW_METADATA clause. 

C. Create an INSTEAD OF trigger on the view. 

D. Modify the view to an indexed view. 

Answer: C 


Q69. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 

Unless stated above, no columns in the Employee table reference other tables. 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. 

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. 

On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1? 

A. DateHired 

B. DepartmentID 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: C 


Q70. DRAG DROP 

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data. 

BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. 

You need to ensure that the new column is assigned a GUID for existing rows. 

Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.) 

Answer: