Online Access Free 070-461 Practice Test

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
* Future modifications to the table definition will not affect the applications' ability to access data.
* The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing applications.
What should you create for each application?

• A.table-valued functions
• B.table partitions
• C.views
• D.stored procedures

You plan to deploy a stored procedure for a database named TICKETS.
You need to implement error handling for the stored procedure to ensure that the system-defined error messages are returned if an error occurs upon insert.
Part of the correct Transact-SQL has been provided in the answer are below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.


Use the 'Check Syntax' button to verify your work. Any syntax or spelling errors will be reported by line and character position.

• A.1 CREATE PROCEDURE AdCase
  2 @CustomerId INT,
  3 @ProblemId INT,
  4 @Comment NVARCHAR (MAX)
  5 AS
  6
  7 BEGIN TRY
  8 INSERT INTO Cases
  9 (CustomerId, ProblemId, Comment)
  10 VALUES
  11 (@CustomerId, @ProblemId, @Comment)
  12 END TRY
  13 BEGIN CATCH
  14 INSERT INTO AppLog
  15 (CurrentTime, ErrorNumber, CustomerId)
  16 VALUES
  17 (getdate(), ERROR_NUMBER(), @CustomerId);
  18 THROW;
  19
  20 END CATCH
  21
  Make changes and additions in the above lines.
  7 BEGIN TRY
  12 END TRY
  13 BEGIN CATCH
  18 THROW;
  20 END CATCH
• B.1 CREATE PROCEDURE AdCase
  2 @CustomerId INT,
  3 @ProblemId INT,
  4 @Comment NVARCHAR (MAX)
  5 AS
  6
  7 BEGIN TRY
  8 INSERT INTO Cases
  9 (CustomerId, ProblemId, Comment)
  10 VALUES
  11 (@CustomerId, @ProblemId, @Comment)
  12 END TRY
  13 END CATCH
  14 Make changes and additions in the above lines.
  15 BEGIN TRY
  16 END TRY
  17 BEGIN CATCH
  18 THROW;
  19END CATCH

You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
Which four 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.)

Correct Answer:

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.Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
• B.Add a FORCESEEK hint to the query.
• C.Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
• D.Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
• E.Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
• F.Add an INCLUDE clause to the index.
• G.Add a HASH hint to the query.
• H.Include a SET STATISTICS PROFILE ON statement before you run the query.
• I.Include a SET FORCEPLAN ON statement before you run the query.
• J.Add a FORCESCAN hint to the Attach query.
• K.Enable the optimize for ad hoc workloads option.
• L.Add a LOOP hint to the query.
• M.Cover the unique clustered index with a columnstore index.
• N.Add a columnstore index to cover the query.

You administer a Microsoft SQL Server database that contains a table named Products.
In a bit column named Discontinued, a value of 1 indicates a product is inactive, and a value of 0 indicates the product is active.
You need to create a view that returns a column with a full product status description.
Which expression should you use to define the column?

• A.DECODE (Discontinued, 0, 'Active', 1, 'Inactive', 'Unknown')
• B.CASE Discontinued
  WHEN 0 THEN 'Active'
  WHEN 1 THEN 'Inactive'
  END
• C.IF(Discontinued = 0, 'Active', 'Inactive')
• D.IF Discontinued = 0
  'Active'
  ELSE
  'Inactive'