70-461 Notes - Real Exam Questions

Practice Our 70-461 Notes Exam Questions and Pass Your Exam Easily.


70-461 Notes

If you are looking for free 70-461 dumps than here we have some sample question answers available. You can prepare from our Microsoft 70-461 exam questions notes and prepare exam with this practice test. Check below our updated 70-461 Notes.

Querying Microsoft SQL Server 2012



Skills measured in this exam are:
Create database objects (24%)
Troubleshoot and optimize (25%)
Modify data (24%)
Work with data (27%)


For Update Follow: For update follow https://twitter.com/MSLearning
For Wiki http://borntolearn.mslearn.net/certification/database/w/wiki/520.461-querying-microsoft-sql-server-2012.aspx
Free 70-461 VCE At: http://dumps4free.com/70-461-exam-questions-pdf-vce.html
Schedule Your Exam At: 


Free Download 70-461 Exam Questions 

Free Download 70-461 Exam Questions 

You can prepare from these 70-461 exam questions or get help from 70-461 dumps for better preparation.

70-461 Dumps


You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

A. Float
B. Money
C. Smallmoney
D. Numeric

Answer: D


A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?

A. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits

B. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits

C. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits

D. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits

Answer: C


You have a database application that uses Microsoft SQL Server 2012. You have a query named Query1 that returns four columns from a frequently updated table that has a clustered index. Three of the columns are referenced in the WHERE clause of the query. The three columns are part of a non-clustered index. The fourth column is not referenced in the WHERE clause. Users report that the application begins to run slowly. You determine that the root cause for the performance issue is Query1. You need to optimize the statement. What should you do?

A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Answer: K

Explanation/Reference:

SET SHOWPLAN_XML (Transact-SQL) causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document. Incorrect:
Not F: Columnstore indexes in the SQL Server Database Engine can be used to significantly speed-up the processing time of common data warehousing queries. Typical data warehousing workloads involve summarizing large amounts of data. But in this question the query is run on a table that is updated frequently, not a warehousing table.
Reference: SET SHOWPLAN_XML (Transact-SQL)


A customer wants to add a fully populated BladeCenter chassis to their existing core-edge SAN. They have suffered performance issues in the past due to congestion and over-utilization of inter- switch links (ISL). The customer has asked what is the best practice guideline concerning ISL oversubscription ratio. Which one of the following ratios does the technical specialist provide?

A. 1:1
B. 10:1
C. 20:1
D. 50:1

Answer: B


You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered index.
The query returns the following six columns:
– One column in its WHERE clause contained in a non-clustered index ·
– Four additional columns
– One COUNT (*) column based on a grouping of the four additional columns
You need to optimize the statement.
What should you do?

A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Answer: F


Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.

Which Transact-SQL query should you use?

A. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
B. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
C. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
D. UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; SELECT ProductCode, Price, Price * 1.05 FROM Products

Answer: A

Section: (none)
Explanation
Explanation/Reference:
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms177564.aspx


You develop a Microsoft SQL Server 2012 database.You need to create a batch process that meets the following 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

Answer : C


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 existing applications.What should you create for each application?

A. views
B. table partitions
C. table-valued functions
D. stored procedures

Answer: A


You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

Answer: A

15% OFF on each Exam Use this discount Code While Payment

 

“5Disc”

Download 70-461 Complete Question & Answers PDF

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

15% OFF on each Exam Use this discount Code While Payment

 

“5Disc”

Download 70-461 Complete Question & Answers PDF

page 1 from 3 next page »