Study with 70-457 Actual Test Questions

You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:
CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

• A. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
• B. ALTER TABLE Inventory ADD TotalItems AS ItemslnStore + ItemsInWarehouse
• C. ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
• D. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

You administer a Microsoft SQL Server 2012 database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from both tables.
You create a new user named UserC. You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC. Which permission or permissions should you assign to UserC? Choose all that apply.

• A. The Select permission on Orders
• B. The Select permission on Customers
• C. The Execute permission on GetCustomerOrderInfo
• D. The Take Ownership permission on Orders
• E. The Take Ownership permission on Customers
• F. The Control permission on GetCustomerOrderInfo

You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS package accesses a network share when exporting data from a SQL Server database. When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package. Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder:

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. SELECT
  CASE
  WHEN convert(decimal(36,9), @var) IS NULL
  THEN 'True'
  ELSE 'False'
  END
  AS BadCast
• B. SELECT
  IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False')
  AS BadCast
• C. 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
• D. TRY( SELECT convert(decimal(36,9), @var) SELECT 'True' AS BadCast
  )
  CATCH(
  SELECT 'False' AS BadCast
  )

You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9) )
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. Which three 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.)
Build List and Reorder: