Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 41:

  You have a table named Person that contains a nvarchar column named Surname. The Person table currently has a clustered index on PersonID.

  The Surname column contains Russian and Japanese characters.

  The following code segment will be used to search by Surname.

  IF @lang ='Russian' SELECT PersonID, Surname FROM Person WHERE Surname = @SearchName COLLATE Cyrillic_General_CI_AS if @lang = 'Japanese' SELECT PersonID, Surname FROM Person WHERE Surname = @SearchName COLLATE Japanese_CI_AS_KS

  You need to enable SQL Server to perform an index seek for these queries.

  What should you do?

  A. Create an index on the Surname column.
  B. Create a computed column for each collation that needs to be searched. Create an index on the Surname column.
  C. Create a computed column for each collation that needs to be searched. Create an index on each computed column.
  D. Create a new column for each collation that needs to be searched and copy the data from the Surname column. Create an index on each new column.
  C. Create a computed column for each collation that needs to be searched. Create an index on each computed column.

  ---

  -- Add computed columns with different collations.

  ALTER TABLE Person

  ADD Surname_RU AS Surname COLLATE Cyrillic_General_CI_AS, Surname_JP AS Surname COLLATE Japanese_CI_AS_KS; -- Create an index on the computed columns.

  CREATE NONCLUSTERED INDEX IX_Person_Surname_RU ON Person (Surname_RU); CREATE NONCLUSTERED INDEX IX_Person_Surname_JP ON Person (Surname_JP); GO

• Question 42:

  You created a Service Broker queue by using the following Transact-SQL statement:

  CREATE QUEUE VacationRequestQueue

  WITH

  RETENTION = OFF,

  ACTIVATION (

  PROCEDURE_NAME = dbo.VacationRequestProcess,

  MAX_QUEUE_READERS = 5,

  EXECUTE AS SELF

  );

  You need to modify the Service Broker queue to prevent it from processing received messages.

  The queue should continue to receive messages.

  Which Transact-SQL statement should you use?

  A. ALTER QUEUE VacationRequestQueue WITH RETENTION = ON;
  B. ALTER QUEUE VacationRequestQueue WITH STATUS = OFF;
  C. ALTER QUEUE VacationRequestQueue WITH ACTIVATION (STATUS = OFF);
  D. ALTER QUEUE VacationRequestQueue WITH ACTIVATION (EXECUTE AS OWNER);
  C. ALTER QUEUE VacationRequestQueue WITH ACTIVATION (STATUS = OFF);

  ---

  ALTER QUEUE VacationRequestQueue WITH RETENTION = ON; sets the VacationRequestQueue queue to retain messages. The queue retains all messages sent to or from services that use this queue until the conversation that contains

  the message ends. ALTER QUEUE VacationRequestQueue WITH STATUS = OFF; makes the VacationRequestQueue queue unavailable to receive messages. ALTER QUEUE VacationRequestQueue WITH ACTIVATION (STATUS =

  OFF); the queue does not activate the stored procedure.

  ALTER QUEUE VacationRequestQueue WITH ACTIVATION (EXECUTE AS OWNER); specifies that the stored procedure executes as the owner of the queue.

• Question 43:

  You are troubleshooting query performance on SQL Server 2008. You are tasked to capture a graphical execution plan. You need to save the plan to a file that can be used by SQL Server Management Studio to display the graphical execution plan.

  Which file extension should you use?

  A. .gif
  B. .xml
  C. .psql
  D. .sqlplan
  D. .sqlplan

• Question 44:

  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. 1. The product will be deleted from the Products table. 2. The order details will be inserted into the OrderDetails table.
  B. 1. The product will be deleted from the Products table. 2. The order details will not be inserted into the OrderDetails table.
  C. 1. The product will not be deleted from the Products table. 2. The order details will be inserted into the OrderDetails table.
  D. 1. The product will not be deleted from the Products table. 2. The order details will not be inserted into the OrderDetails table.
  D. 1. The product will not be deleted from the Products table. 2. The order details will not be inserted into the OrderDetails table.

  ---

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

  You have a third-party application that inserts data directly into a table. You add two new columns to the table. These columns cannot accept NULL values and cannot use default constraints. You need to ensure that the new columns do not break the third-party application.

  What should you do?

  A. Create a DDL trigger.
  B. Create a stored procedure.
  C. Create an AFTER INSERT trigger.
  D. Create an INSTEAD OF INSERT trigger.
  D. Create an INSTEAD OF INSERT trigger.

• Question 46:

  You have two views named Sales.SalesSummaryOverall and Sales.CustomerAndSalesSummary. They are defined as follows:

  CREATE VIEW Sales.SalesSummaryOverall AS SELECT CustomerId, SUM(SalesTotal) AS OverallTotal FROM Sales.SalesOrder GROUP BY CustomerId GO CREATE VIEW Sales.CustomerAndSalesSummary AS SELECT Customer.Name, SalesSummaryOverall.OverallTotal, (SELECT AVG(OverallTotal) FROM Sales.SalesSummaryOverall WHERE SalesSummaryOverall.CustomerId = Customer.CustomerId) AS avgOverallTotal, (SELECT MAX(OverallTotal) FROM Sales.SalesSummaryOverall WHERE SalesSummaryOverall.CustomerId = Customer.CustomerId) AS maxOverallTotal, FROM Sales.Customer LEFT OUTER JOIN Sales. Sales.SalesSummaryOverall ON SalesSummaryByYear.CustomerId = Customer.CustomerId GO You have been tasked to modify the Sales.CustomerAndSalesSummary view to remove references to other views. You need to identify a feature to use in the modified version of the Sales.CustomerAndSalesSummary object to achieve the task.

  Which feature should you use?

  A. Table variables
  B. Temporary tables
  C. User-defined table types
  D. Common table expressions
  D. Common table expressions

• Question 47:

  You create and populate two tables by using the following Transact-SQL statements:

  CREATE TABLE CurrentStudents (LastName VARCHAR(50), FirstName VARCHAR(50),

  Address VARCHAR(100),

  Age INT);

  INSERT INTO CurrentStudents

  VALUES ('Fritz', 'David', '181 Kline Street', 14)

  ,('Reese', 'Paul' , '4429 South Union', 14)

  ,('Brown', 'Jake' , '5401 Washington Ave',14)

  ,('Smith', 'Tom' , '124 Water St', 14)

  ,('Holtz', 'Mary' , '984 Mass Ct', 14)

  ,('Robbins', 'Jan' , '4449 Union Ave', 14)

  ,('Larsen', 'Frank' , '5812 Meadow St', 14)

  ,('Bishop', 'Cathy' , '14429 Skyhigh Ave', 14)

  ,('Francis', 'Thomas' , '15401 120th St', 14)

  CREATE TABLE NewYearRoster(LastName VARCHAR(50),

  FirstName VARCHAR(50),

  Address VARCHAR(100),

  Age INT);

  INSERT INTO NewYearRoster

  VALUES ('Fritz', 'David', '181 Kline Street', 15)

  ,('Reese', 'Paul', '1950 Grandview Place', 15)

  ,('Adams', 'Wilbur', '4231 W. 93rd', 15)

  ,('Adams', 'Norris', '100 1st Ave', 15)

  ,('Thomas', 'Paul', '18176 Soundview Dr', 15)

  ,('Linderson', 'Danielle', '941 W. 37 Ave', 15)

  ,('Moore', 'Joshua', '2311 10st Ave', 15)

  ,('Dark', 'Shelby', '1987 Fifth Ave', 15)

  ,('Scharp', 'Mary', '1902 W. 303rd', 15)

  ,('Morris', 'Walt', '100 12st St', 15);

  You run the following MERGE statement to update, insert and delete rows in the CurrentStudents table:

  MERGE TOP (3) CurrentStudents AS T

  USING NewYearRoster AS S

  ON S.LastName = T.LastName AND S.FirstName = T.FirstName WHEN MATCHED AND NOT (T.Age = S.Age OR T.Address = S.Address) THEN UPDATE SET Address = S.Address,

  Age = S.Age

  WHEN NOT MATCHED BY TARGET THEN

  INSERT (LastName, FirstName, Address, Age)

  VALUES (S.LastName, S.FirstName, S.Address, S.Age) WHEN NOT MATCHED BY SOURCE THEN

  DELETE;

  You need to identify the total number of rows that are updated, inserted, and deleted in the CurrentStudent table.

  Which total number of rows should you choose?

  B. 3
  C. 6
  D. 9
  B. 3

• Question 48:

  You have a single CLR assembly in your database. The assembly only references blessed assemblies from the Microsoft .NET Framework and does not access external resources.

  You need to deploy this assembly by using the minimum required permissions. You must ensure that your database remains as secure as possible.

  Which options should you set?

  A. PERMISSION_SET = SAFE TRUSTWORTHY ON
  B. PERMISSION_SET = SAFE TRUSTWORTHY OFF
  C. PERMISSION_SET = UNSAFE TRUSTWORTHY ON
  D. PERMISSION_SET = EXTERNAL_ACCESS TRUSTWORTHY OFF
  B. PERMISSION_SET = SAFE TRUSTWORTHY OFF

• Question 49:

  You need to identify, within a given clause, if the month of February will contain 29 days for a specified year.

  Which object should you use?

  A. DML trigger
  B. Stored procedure
  C. Table-valued function
  D. Scalar-valued function
  D. Scalar-valued function

• Question 50:

  You have been tasked to write a query to select one million rows. You need to optimize the query to return the first 50 rows as quickly as possible.

  What query hint should you use?

  A. FAST 50
  B. MAXDOP 50
  C. OPTIMIZE FOR @ROWS=50
  D. TABLE HINT(table, INDEX(50))
  A. FAST 50

  ---

  FAST number_rows

  Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.