New Braindump2go 70-433 Exam Questions Updated Today! Want to know New Questions in 2015 70-433 Exam? Download Free Braindump2go 70-433 Exam Preparation Materials Now!
Exam Code: 70-433
Exam Name: TS: Microsoft SQL Server 2008, Database Development
Certification Provider: Microsoft
Keywords: 70-433 Exam Dumps,70-433 Practice Tests,70-433 Practice Exams,70-433 Exam Questions,70-433 PDF,70-433 VCE Free,70-433 Book,70-433 E-Book,70-433 Study Guide,70-433 Braindump,70-433 Prep Guide
QUESTION 101
You have created an assembly that utilizes unmanaged code to access external resources.
You need to deploy the assembly with the appropriate permissions.
Which permission set should you use?
A. SAFE
B. UNSAFE
C. EXTERNAL_ACCESS
D. Default permission set
Answer: B
QUESTION 102
You have tables named Products and OrderDetails.
The Products table has a foreign key relationship with the OrderDetails table on the ProductID column.
You have the following Transact-SQL batch:
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM Products WHERE ProductID = 5;
BEGIN TRANSACTION
INSERT INTO OrderDetails
( OrderID, ProductID, Quantity )
VALUES
( 1234, 5, 12 );
COMMIT TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH ROLLBACK TRANSACTION PRINT ERROR_MESSAGE();
END CATCH
You need to analyze the result of executing this batch.
What should be the expected outcome?
A. –The product will be deleted from the Products table.
–The order details will be inserted into the OrderDetails table.
B. –The product will be deleted from the Products table.
–The order details will not be inserted into the OrderDetails table.
C. –The product will not be deleted from the Products table.
–The order details will be inserted into the OrderDetails table.
D. –The product will not be deleted from the Products table.
–The order details will not be inserted into the OrderDetails table.
Answer: D
Explanation:
ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ]
[ ; ]
transaction_name
Is the name assigned to the transaction on BEGIN TRANSACTION. When nesting transactions,
transaction_name must be the name from the outermost BEGIN TRANSACTION statement.
savepoint_name
Is savepoint_name from a SAVE TRANSACTION statement. Use savepoint_name when a conditional rollback should affect only part of the transaction.
ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed, except when the COMMIT TRANSACTION is associated with a nested transaction that is contained within the transaction being rolled back. In this instance, the nested transaction will also be rolled back, even if you have issued a COMMIT TRANSACTION for it.
SQL Server 2008 error handling best practice
CREATE PROCEDURE SaveTranExample
@InputCandidateID INT
AS
— Detect whether the procedure was called from an active transaction and save that for later use.
— In the procedure, @hasOuterTransaction = 0 means there was no active transaction
— and the procedure started one.
— @hasOuterTransaction > 0 means an active transaction was started before the
— procedure was called.
DECLARE @hasOuterTransaction BIT = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0
END;
— Save points need unique names if modules can nest otherwise you can rollback
— to the wrong save point. The solution is to use a GUID to name the save points.
DECLARE @rollbackPoint nchar(32) = REPLACE(CONVERT(NCHAR(36), NEWID()), N’-‘, N”);
IF @hasOuterTransaction > 0
BEGIN
— Procedure called when there is an active transaction.
— Create a savepoint to be able to roll back only the work done in the procedure if there is an error.
SAVE TRANSACTION @rollbackPoint;
END
ELSE
— Procedure must start its own transaction.
BEGIN TRANSACTION @rollbackPoint;
— Modify database.
BEGIN TRY
— Do work;
DELETE HumanResources.JobCandidate
WHERE JobCandidateID = @InputCandidateID;
— Get here if no errors; must commit
— any transaction started in the
— procedure, but not commit a transaction
— started before the transaction was called.
IF @hasOuterTransaction = 0
BEGIN
— @hasOuterTransaction = 0 means no transaction was started before the procedure was called.
— The procedure must commit the transaction it started.
COMMIT TRANSACTION;
END
END TRY
BEGIN CATCH
— An error occurred;
— If the transaction is still valid
IF XACT_STATE() = 1
— The XACT_STATE function can return the following values:
— 1 An open transaction exists that can be either committed or rolled back.
— 0 There is no open transaction.
— -1 An open transaction exists, but it is in a doomed state. Due to the type of error that was raised, the transaction can only be rolled back.
BEGIN
— Because the syntax for ROLLBACK TRANSACTION is the same for the transaction and for a savepoint
— (ROLLBACK TRANSACTION [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ])
— we can write the following:
ROLLBACK TRANSACTION @rollbackPoint;
— In case @rollbackPoint has the name of a transaction, roll back to the beginning of the transaction.
— In case @rollbackPoint has the name of a savepoint, roll back to the savepoint.
END;
ELSE IF XACT_STATE() = -1
BEGIN
IF @hasOuterTransaction = 0
BEGIN
— Transaction started in procedure.
— Roll back complete transaction.
ROLLBACK TRANSACTION;
END
— If the transaction is uncommitable, a rollback to the savepoint is not allowed
— because the savepoint rollback writes to the log. Just return to the caller, which
— should roll back the outer transaction.
END
— Execute Standard module error handler;
— After the appropriate rollback, echo error information to the caller.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, — Message text.
@ErrorSeverity, — Severity.
@ErrorState — State.
);
END CATCH
GO
QUESTION 103
You are using TRY…CATCH error handling.
You need to raise an error that will pass control to the CATCH block.
Which severity level should you use?
A. 0
B. 9
C. 10
D. 16
Answer: D
Explanation:
A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.
Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.
Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.
QUESTION 104
You have a table named Orders.
You have been tasked to modify your company’s main database to remove all inactive order rows. You are developing a stored procedure that will enable you to delete these rows.
You have written the following code segment to accomplish this task. (Line numbers are included for reference only.)
01 BEGIN TRY
02 DECLARE @RowCount INT = 1000
03 WHILE @RowCount = 1000
04 BEGIN
05 DELETE TOP (1000) FROM Orders WHERE Status = ‘Inactive’;
06 SET @RowCount = @@ROWCOUNT
07 …
08 END
09 END TRY
10 BEGIN CATCH
11 PRINT ERROR_MESSAGE()
12 END CATCH
You need to insert a Transact-SQL statement that will notify you immediately after each batch of rows is deleted.
Which Transact-SQL statement should you insert at line 07?
A. RAISERROR (‘Deleted %i rows’, 6, 1, @RowCount)
B. RAISERROR (‘Deleted %i rows’, 16, 1, @RowCount)
C. RAISERROR (‘Deleted %i rows’, 10, 1, @RowCount) WITH NOWAIT
D. RAISERROR (‘Deleted %i rows’, 11, 1, @RowCount) WITH NOWAIT
Answer: C
Explanation:
RAISERROR can be used as an alternative to PRINT to return messages to calling applications.
Because RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block, specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block. NOWAIT option sends messages immediately to the client.
QUESTION 105
You have a transaction that uses the repeatable read isolation level.
This transaction causes frequent blocking problems.
You need to reduce blocking.
You also need to avoid dirty reads and non-repeatable reads.
Which transaction isolation level should you use?
A. SNAPSHOT
B. SERIALIZABLE
C. READ COMMITTED
D. READ UNCOMMITTED
Answer: A
QUESTION 106
You are writing a batch that contains multiple UPDATE statements to modify existing products. You have placed these updates into one explicit transaction.
You need to set an option at the beginning of the transaction to roll back all changes if any of the updates in the transaction fail.
Which option should you enable?
A. ARITHABORT
B. XACT_ABORT
C. IMPLICIT_TRANSACTIONS
D. REMOTE_PROC_TRANSACTIONS
Answer: B
QUESTION 107
You have a table named JobCandidate.
You are tasked to delete a row in the JobCandidate table.
You need to write a transaction that allows the database to be restored to the exact point the record was deleted without knowing the time of execution.
Which query should you use?
A. BEGIN TRANSACTION
DELETE FROM JobCandidate
WHERE JobCandidateID = 10;
COMMIT TRANSACTION;
B. BEGIN TRANSACTION WITH MARK N’Deleting a Job Candidate’;
DELETE FROM JobCandidate
WHERE JobCandidateID = 10;
COMMIT TRANSACTION
C. BEGIN TRANSACTION Delete_Candidate WITH MARK
DELETE FROM JobCandidate
WHERE JobCandidateID = 10;
COMMIT TRANSACTION Delete_Candidate;
D. DECLARE @CandidateName varchar(50) = ‘Delete_Candidate’
BEGIN TRANSACTION @CandidateName
DELETE FROM JobCandidate
WHERE JobCandidateID = 10;
COMMIT TRANSACTION @CandidateName;
Answer: C
Explanation:
BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ ‘description’ ] ]
]
[ ; ]
WITH MARK [ ‘description’ ] – specifies that the transaction is marked in the log. description is a string that describes the mark.
If WITH MARK is used, a transaction name must be specified. When restoring a database to an earlier state, the marked transaction can be used in place of a date and time.
The mark is placed in the transaction log only if the database is updated by the marked transaction.
Transactions that do not modify data are not marked.
BEGIN TRAN new_name WITH MARK can be nested within an already existing transaction that is not marked.
Upon doing so, new_name becomes the mark name for the transaction, despite the name that the transaction may already have been given.
QUESTION 108
You have the following table named Sales.
You need to return sales data ordered by customer name and date of sale.
For each customer, the most recent sale must be listed first.
Which query should you use?
A. SELECT CustomerName,
SalesDate
FROM Sales
ORDER BY CustomerName,
SalesDate;
B. SELECT CustomerName,
SalesDate
FROM Sales
ORDER BY SalesDate DESC,
CustomerName;
C. SELECT CustomerName,
SalesDate
FROM Sales
ORDER BY CustomerName,
SalesDate DESC;
D. SELECT CustomerName,
SalesDate
FROM Sales
ORDER BY CustomerName DESC;
Answer: C
QUESTION 109
You have a table named Sales.SalesOrderHeader and a table named Person.Person.
You are tasked to write a query that returns SalesOrderID and SalesPersonName that have an OrderDate greater than 20040101.
SalesPersonName should be made up by concatenating the columns named FirstName and LastName from the table named Person.Person.
You need to write a query to return data, sorted in alphabetical order, by the concatenation of FirstName and LastName.
Which Transact-SQL statement should you use?
A. SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName
FROM Sales.SalesOrderHeader H
JOIN Person.Person P on
BusinessEntityID = H.SalesPersonID
WHERE OrderDate > ‘20040101’
ORDER BY FirstName ASC, LastName ASC
B. SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName
FROM Sales.SalesOrderHeader H
JOIN Person.Person P on
BusinessEntityID = H.SalesPersonID
WHERE OrderDate > ‘20040101’
ORDER BY FirstName DESC, LastName DESC
C. SELECT SalesOrderID, FirstName +’ ‘ + LastName as SalesPersonName
FROM Sales.SalesOrderHeader H
JOIN Person.Person P on
BusinessEntityID = H.SalesPersonID
WHERE OrderDate > ‘20040101’
ORDER BY SalesPersonName ASC
D. SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName
FROM Sales.SalesOrderHeader H
JOIN Person.Person P on
BusinessEntityID = H.SalesPersonID
WHERE OrderDate > ‘20040101’
ORDER BY SalesPersonName DESC
Answer: C
QUESTION 110
You have a table named Sales.PotentialClients.
This table contains a column named EmailAddress.
You are tasked to develop a report that returns valid “.com” email addresses from Sales.PotentialClients.
A valid email address must have at least one character before the @ sign, and one character after the @ sign and before the “.com.”
You need to write a Transact-SQL statement that returns data to meet the business requirements.
Which Transact-SQL statement should you use?
A. select * from Sales.PotentialClients
where EmailAddress like ‘_%@_%.com’
B. select * from Sales.PotentialClients
where EmailAddress like ‘%@%.com’
C. select * from Sales.PotentialClients
where EmailAddress like ‘_%@_.com’
D. select * from Sales.PotentialClients
where EmailAddress like ‘%@%[.]com’
Answer: C
Explanation:
If “at least” expression refer to both “one character before…” and “one character after…”,
then A is correct.
If “at least” expression refer to only “one character before…” which seems the case,
then C is correct.
Instant Download Braindump2go New Released Microsoft 70-433 Exam Dumps PDF & VCE! Enjoy 1 year Free Updation! 100% Exam Pass Guaranteed Or Full Money Back!
