During your 70-461 examination planning, it is crucial that you just review the examination guidebook frequently to ensure that most of the supplies during the 70-461 examination guidebook usually are included in your personal choice of planning approaches. There is lots of strategy concerning this special 70-461 examination internet, you could watch over many internet site and have the theory about the review content and also other items that you could grab the help. With a a number of means is very encouraged for you to organize this unique Microsoft 70-461 check.

2021 Mar 70-461 exam prep

Q1. You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected. 

You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer. 

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 


Q2. 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: 


Q3. 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: 


Q4. You are developing a database application by using Microsoft SQL Server 2012. 

An application that uses a database begins to run slowly. 

You discover that during reads, the transaction experiences blocking from concurrent updates. 

You need to ensure that throughout the transaction the data maintains the original version. 

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: M 


Q5. You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. 

You need to assign a PartitionNumber for each record in the Customers table.You also need to ensure that the PartitionNumber satisfies the following conditions: 

. Always starts with 1. 

. Starts again from 1 after it reaches 100. 

Which Transact-SQL statement should you use? 

A. CREATE SEQUENCE CustomerSequence AS int START WITH 0 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

B. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

C. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence 

D. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100 CYCLE UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

Answer: B 


Latest 70-461 free exam questions:

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

You need to ensure that if the stored procedure raises a run-time 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 XACT_ABORT ON 

B. SET ARITHABORT ON 

C. TRY 

D. BEGIN 

E. SET ARITHABORT OFF 

F. SET XACT_ABORT OFF 

Answer: A 


Q7. 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 


Q8. 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: 


Q9. DRAG DROP 

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

You create a table by using the following definition: 

CREATE TABLE Prices ( 

PriceId int IDENTITY(1,1) PRIMARY KEY, 

ActualPrice NUMERIC(16,9), 

PredictedPrice NUMERIC(16,9) 

) 

You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. 

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


Q10. You are a database developer of a Microsoft SQL Server 2012 database. 

You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. 

A sample of this data is as shown in the following table. 

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. 

Which Transact- SQL statement should you use? 

A. CREATE TABLE Customer 

(SourceID int NOT NULL IDENTITY, 

CustomerID int NOT NULL IDENTITY, 

CustomerName varchar(255) NOT NULL); 

B. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerName varchar(255) NOT NULL); 

C. CREATE TABLE Customer 

(SourceID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerID int NOT NULL UNIQUE, 

CustomerName varchar(255) NOT NULL); 

D. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL, 

CustomerName varchar(255) NOT NULL, 

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED 

(SourceID, CustomerID)); 

Answer: D