Latest MCSE 70-461 Real Exam Download 31-40

 

QUESTION 31

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:

Status information must be logged to a status table.

If the status table does not exist at the beginning of the batch, it must be created.

Which object should you use?

A. Scalar user-defined function

B. Inline user-defined function

C. Table-valued user-defined function

D. Stored procedure

 

Correct Answer: D

 

 

QUESTION 32

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

 

Correct Answer: B

 

 

QUESTION 33

You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

 

 

Id is the Primary Key.

You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. Which Transact-SQL statement should you use?

 

A. UPDATE TOP(10) BlogEntry

SET Summary.WRITE(N’ This is in a draft stage’, NULL, 0)

B. UPDATE BlogEntry

SET Summary = CAST(N’ This is in a draft stage’ as nvarchar(max))

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

C. UPDATE BlogEntry

SET Summary.WRITE(N’ This is in a draft stage’, NULL, 0) FROM (

SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s

WHERE BlogEntry.Id = s.ID

D. UPDATE BlogEntry

SET Summary.WRITE(N’ This is in a draft stage’, 0, 0)

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

 

Correct Answer: C

 

 

QUESTION 34

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

 

A. DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;

 

EXEC DeleteJobCandidate

 

SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0)

PRINT N’Error = ‘ + CAST(@@ErrorVar AS NVARCHAR(8)) +

N’, Rows Deleted = ‘ + CAST(@@RowCountVar AS NVARCHAR(8));

GO

B. DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;

 

EXEC DeleteJobCandidate

 

SELECT @ErrorVar = ERROR_STATE(), @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0)

PRINT N’Error = ‘ + CAST(ERRORSTATE() AS NVARCHAR(8)) +

N’, Rows Deleted = ‘ + CAST(@@RowCountVar AS NVARCHAR(8));

GO

C. EXEC DeleteJobCandidate

 

IF (ERROR_STATE() != 0)

PRINT N’Error = ‘ + CAST(@@ERROR AS NVARCHAR(8)) +

N’, Rows Deleted = ‘ + CAST(@@ROWCOUNT AS NVARCHAR(8));

GO

D. EXEC DeleteJobCandidate

 

PRINT N’Error = ‘ + CAST(@@ERROR AS NVARCHAR(8)) +

N’, Rows Deleted = ‘ + CAST(@@ROWCOUNT AS NVARCHAR(8));

GO

 

Correct Answer: A

 

 

QUESTION 35

You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)

 

 

The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

 

A. IF @@TRANCOUNT = 0

B. IF (XACT_STATE ( ) ) = 0

C. IF (XACT_STATE ( ) ) = 1

D. IF @@TRANCOUNT = l

 

Correct Answer: C

 

 

QUESTION 36

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 preceding year. Which Transact-SQL query should you use?

 

A. SELECT Territory, Year, Profit,

LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS

NextProfit

FROM Profits

B. SELECT Territory, Year, Profit,

LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS

NextProfit

FROM Profits

C. SELECT Territory, Year, Profit,

LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS

NextProfit

FROM Profits

D. SELECT Territory, Year, Profit,

LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS

NextProfit

FROM Profits

 

Correct Answer: B

 

 

QUESTION 37

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use?select

A. BEGIN TRY

SELECT convert(decimal(36,9), @var) AS Value, ‘True’ AS BadCast

END TRY

BEGIN CATCH

SELECT convert(decimal(36,9), @var) AS Value, ‘False’ AS BadCast

END CATCH

B. TRY(

SELECT convert(decimal(36,9), @var)

SELECT ‘True’ AS BadCast

)

CATCH(

SELECT ‘False’ AS BadCast

)

C. SELECT CASE

WHEN convert(decimal(36,9), @var) IS NULL THEN ‘True’

ELSE ‘False’ END

AS BadCast

D. SELECT

IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, ‘True’, ‘False’)

AS BadCast

 

Correct Answer: D

 

 

QUESTION 38

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

 

Correct Answer: E

 

 

QUESTION 39

You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerID. You need to create a query that meets the following requirements:

Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. Results must include customers who have not placed any orders.

Which Transact-SQL query should you use? A. SELECT CustomerName, OrderDate

FROM Customers

RIGHT OUTER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID

B. SELECT CustomerName, CrderDate

FROM Customers

JOIN Orders

ON Customers.CustomerID = Orders.CustomerID

C. SELECT CustomerName, OrderDate

FROM Customers