70-461 | A table named Profits stores the total…

Question: 22

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

70-461 | You have a database application that…..

Question: 21

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)

70-461|A customer wants to add a fully populated

Question: 34

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

70-461|You are developing a database application by using Microsoft SQL Server 2012

Question: 33

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

70-461|Which Transact-SQL query should you use?

Question: 32

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

70-461 | 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.

Question : 31

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

70-461 | You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.

Question : 30

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

70-461 Sample Question : 14

Question : 14

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

70-461 Sample Question : 13

Question : 13

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

70-461 Sample Question : 12

Question : 12

You administer a Microsoft SQL Server database that supports a banking transaction management application. You need to retrieve a list of account holders who live in cities that do not have a branch location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)

Answer: AB

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

“5Disc”

Download 70-461 Complete Question & Answers PDF

70-461 Sample Question : 11

Question : 11

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

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

“5Disc”

Download 70-461 Complete Question & Answers PDF

70-461 Sample Question : 10

Question : 10

You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:
CREATE TABLE Inventory
(ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. Which Transact-SQL statement should you use?

A.  ALTER TABLE InventoryADD TotalItems AS ItemsInStore + ItemsInWarehouse
B.  ALTER TABLE InventoryADD ItemsInStore – ItemsInWarehouse = TotalItems
C.  ALTER TABLE InventoryADD TotalItems = ItemsInStore + ItemsInWarehouse
D.  ALTER TABLE InventoryADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

Answer: A

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

“5Disc”

Download 70-461 Complete Question & Answers PDF

70-461 Sample Question : 9

 Question : 9

You work as a database developer at ABC.com. ABC has an in-house application named ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database.You want to run an execution plan against the query that will provide detailed information on missing indexes.How would you accomplish this task?

A. You should make use of the READPAST hint in the queries.

B. You should make use of the READCOMMITTED hint in the queries.

C. You should make use of the SET SHOWPLAN_XML ON statement in the query.

D. You should make use of the SET STATISTICS XML ON statement in the query.

E. You should make use of the SET XACT_ABORT OFF statement in the query.

F. You should make use of the SET CONTEXT_INFO statement in the query.

Answer: C

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

“5Disc”

Download 70-461 Complete Question & Answers PDF

70-461 Sample Question : 8

Question : 8

You work as a database administrator at manufacturing company named ABC.com. ABC.com has a SQL Server 2012 database named ProductionDB. The ProductionDB database has a table named Sites that was created using the following Transact-SQL code:CREATE TABLE Sites (SiteID int NOT NULL PRIMARY KEY,Location int NOT NULL,Manager nvarchar(200) NOT NULL,Week smallint NOT NULL,ItemsProduced int NOT NULL )You want to write the Transact-SQL query that returns the number of items produced at each location for each week. In addition, you want the result set to include a column named PrevItemsProduced that holds the number of items produced at each location for the week before.What Transact SQL statement would accomplish this task?

A. SELECT Location, Week, ItemsProduced,LEAD(Items Produced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) ASPrevItems Produced FROM Sites

B. SELECT Location, Week, ItemsProduced,FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) ASPrevItems Produced FROM Sites

C. SELECT Location, Week, ItemsProduced,LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) ASPrevItemsProduced FROM Sites

D. SELECT Location, Week, ItemsProduced,LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) ASPrevItemsProduced FROM Sites

E. SELECT Location, Week, ItemsProduced,CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites

Answer: C

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

“5Disc”

Download 70-461 Complete Question & Answers PDF

70-461 Sample Question : 7

Question : 7

Which of the following can be used to protect the code in a stored procedure?

A. The ENCRYPBYKEY statement.

B. The ENCRYPBYASYMKEY statement.

C. The SET TRUSTWORTHY ON option.

D. The SET XACT_ABORT ON statement.

E. The ENCRYPTBYPASSPHRASE statement.

F. The ENCRYPTBYCERT statement.

G. The SIGNBYASYMKEY statement.

H. The CRYPT_GEN_RANDOM statement.

Answer: A

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

“5Disc”

Download 70-461 Complete Question & Answers PDF
Search Words: 70-461 Notes real exam questions answers practice test braindumps, free Q&A online dumps download and free 70-461 Notes discount coupon code available.

Leave a Comment.

1 × three =