Ensurepass

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
Explanation/Reference:
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms186755.aspx

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
Explanation/Reference:
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/ff878091.aspx

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: D
Explanation/Reference:
Verified answer as correct.

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
Explanation/Reference:
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms190193.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms188790.aspx

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
Explanation/Reference:
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms189797.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms187967.aspx

Download Ensurepass Latest 2013 MCSA 70-461 Real Exam Questions , help you to pass exam 100%.

Comments are closed.