Microsoft 70-457 Value Pack (Frequently Bought Together)

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. You
attempt to add the [Corpnet\User1] login to the database. However, you receive the following error
message:
"User already exists in current database."
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the
original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?

• A. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
• B. ALTER SERVER ROLE [sysadmin] ADD MEMBER [Corpnet\User1];
• C. DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];
• D. ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between
09:00
hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. These data load operations must occur in the minimum amount of time. A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that the backup size is as small as possible. Which backup should you perform every two hours?

• A. NO_CHECKSUM
• B. BULK_LOGGED
• C. DBO.ONLY
• D. Differential
• E. SIMPLE
• F. Transaction log
• G. NORECOVERY
• H. CHECKSUM
• I. RESTART
• J. CONTINUE_AFTER_ERROR
• K. FULL
• L. SKIP
• M. COPY_ONLY
• N. STANDBY

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

You create a view named VwEmployee as shown in the following Transact-SQL statement.

Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view. Which Transact-SQL statement should you use?

• A. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN
  DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15)
  SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName,
  @EmployeeNumber = EmployeeNumber
  FROM inserted
  INSERT INTO Person(Id, FirstName, LastName)
  VALUES(@ID, @FirstName, @LastName)
  INSERT INTO Employee(PersonID, EmployeeNumber)
  VALUES(@PersonID, @EmployeeNumber
  End
• B. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee
  INSERT INTO Employee(PersonID, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM VwEmployee
  End
• C. CREATE TRIGGER TrgVwEmployee ON VwEmployee FOR INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted
  INSERT INTO Employee(PersonId, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM inserted
  END
• D. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted
  INSERT INTO Employee(PersonId, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM inserted
  END

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks:
Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports. You need to improve the performance of the reports.
What should you do?

• A. Convert the view into a Common Table Expression (CTE).
• B. Convert the view into an indexed view.
• C. Convert the view into a table-valued function.
• D. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. You write the following Transact-SQL query:
INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?

• A. ROWLOCK
• B. XLOCK
• C. HOLDLOCK
• D. UPDLOCK
• E. TABLOCK