What tells you about exam 70 461 dumps

Proper study guides for Most up-to-date Microsoft Querying Microsoft SQL Server 2012 certified begins with Microsoft 70 461 certification preparation products which designed to deliver the Breathing 70 461 vce questions by making you pass the 70 461 dumps test at your first time. Try the free 70 461 exam questions demo right now.

Q51. You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. 

Which type of user should you create? 

A. SQL user without login 

B. SQL user with a custom SID 

C. SQL user with login 

D. Domain user 

Answer: A 

Q52. You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do? 

A. Set the Merge agent on the problem subscribers to use the slow link agent profile. 

B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication. 

C. Change the Merge agent on the problem subscribers to run continuously. 

D. Set the Remote Connection Timeout on the Publisher to 0. 

Answer: A 

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

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

Which approach should you use? 

A. A recursive stored procedure 

B. Trigger 

C. An UPDATE statement that includes CASE 

D. Cursor 

E. The foreach SQLCLR statement 

Answer: D 

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

A. ENCRYPTBYKEY 

B. ENCRYPTION 

C. ENCRYPTBYPASSPHRASE 

D. ENCRYPTBYCERT 

Answer: B 

Q55. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. 

You need to ensure that the following requirements are met: 

Students must be ranked based on their average marks. 

If one or more students have the same average, the same rank must be given to 

these students. 

Consecutive ranks must be skipped when the same rank is assigned. 

Which Transact-SQL query should you use? 

A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks 

C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

Answer: A 

Q56. You use Microsoft SQL Server 2012 to develop a database application. 

You create a stored procedure named dbo.ModifyData that can modify rows. 

You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: 

. Does not return an error 

. Closes all opened transactions 

Which Transact-SQL statement should you use? 

A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH 

B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW; END CATCH 

C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData 

COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH 

D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH 

Answer: D 

Q57. CORRECT TEXT 

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button). 

You need to create a query for a report. The query must meet the following requirements: 

NOT use object delimiters. 

Return the most recent orders first. 

Use the first initial of the table as an alias. 

Return the most recent order date for each customer. 

Retrieve the last name of the person who placed the order. 

Return the order date in a column named MostRecentOrderDate that appears as 

the last column in the report. The solution must support the ANSI SQL-99 standard. 

Which code segment should you use? 

To answer, type the correct code in the answer area. 

Answer: 

Q58. You develop a Microsoft SQL Server 2012 database. The database is used by two web 

applications that access a table named Products. 

You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. 

You need to ensure that the following 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 applications. 

What should you create for each application? 

A. Synonyms 

B. Common table expressions 

C. Views 

D. Temporary tables 

Answer: C 

Q59. DRAG DROP 

You write the following SELECT statement to get the last order date for a particular customer. 

You need to create the user-defined function to return the last order date for the specified customer. 

Which five 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: 

Q60. You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers. 

The view has the following definition: 

You need to ensure that users can update only the phone numbers and email addresses by using this view. 

What should you do? 

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement. 

B. Create an INSTEAD OF UPDATE trigger on the view. 

C. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view. 

D. Create an AFTER UPDATE trigger on the view. 

Answer: B