070-457 무료 덤프문제 온라인 액세스

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)
• B.ALTER TABLE Inventory ADD TotalItems AS ItemslnStore + ItemsInWarehouse
• C.ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
• D.ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?

• A.Add a clustered index on SalesOrderId in SalesOrderHeader.
• B.Use a FORCESCAN hint in the query.
• C.Use a FORCESEEK hint in the query.
• D.Update statistics on SalesOrderId on both tables.

You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition:

You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?

• A.CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber)
  - - Create Audit Records
• B.CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted)
  - - Create Audit Records
• C.CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber)
  - - Create Audit Records
• D.CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber)
  - - Create Audit Records

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 Execute permission on GetCustomerOrderInfo
• B.The Control permission on GetCustomerOrderInfo
• C.The Take Ownership permission on Customers
• D.The Select permission on Customers
• E.The Take Ownership permission on Orders
• F.The Select permission on Orders

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_STATE(), @RowCountVar = @@ROWCOUNT;
  IF (@ErrorVar <> 0)
  PRINT N'Error = ' + CAST(ERRORSTATE() AS NVARCHAR(8)) +
  N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
  GO
• B.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
• 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