Microsoft 070-464 Actual Tests : Developing Microsoft SQL Server Databases

Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance named Instance1. Instance1 contains a database named ContentDatabase.
ContentDatabase uses transaction log backups.
The recovery model of ContentDatabase is set to FULL.
You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log.
Which three code segments should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

Explanation:
Note:
* Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.
Transact-SQL
USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO
* If the log file does not shrink (after dbcc shrinkfile)
Typically it is the log file that appears not to shrink. This is usually the result of a log file that has notbeen truncated. You can truncate the log by setting the database recovery model to SIMPLE, or bybacking up the log and then running the DBCC SHRINKFILE operation again.
* DBCC SHRINKFILEShrinks the size of the specified data or log file for the current database, or empties a file by moving thedata from the specified file to other files in the same filegroup, allowing the file to be removed from thedatabase.
Arguments include:
target_size
Is the size for the file in megabytes, expressed as an integer.

You create a view by using the following code:

Several months after you create the view, users report that the view has started to return unexpected results.
You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results.
Which code segment should you run?

• A. Option C
• B. Option B
• C. Option A
• D. Option D

You are evaluating the index design.
You need to recommend a change to Indexes.sql that minimizes the amount of time it takes for usp_SelectEmployessByName to execute.
Which line of code should you use to replace line 08 of Indexes.sql?

• A. (EmployeeID) INCLUDE (LastName);
• B. (LastName) INCLUDE (FirstName);
• C. (LastName, EmployeeID);
• D. (FirstName, LastName);

Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).

You discover that the usp_GetOrdersAndItems stored procedure takes a long time to complete while usp_AddOrder or usp_AddXMLOrder run.
You need to ensure that usp_GetOrdersAndItems completes as quickly as possible.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SERIALIZABLE.
• B. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SNAPSHOT.
• C. Set the isolation level of the usp_AddOrder stored procedure to SERIALIZABLE.
• D. Set the isolation level of the usp_AddOrder stored procedure to SNAPSHOT.
• E. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION OFF statement.
• F. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON statement.

You need to design the UserActivity table.
Which three steps should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order.

1 - Create a clustered columnstore index.
2 - Create a partitioning scheme for use by the table.
3 - Use an ALTER INDEX REORGANIZE on a specific partition.
Explanation:
Note:
* Creating a partitioned table or index typically happens in four parts:
Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column.
Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
Create or modify a table or index and specify the partition scheme as the storage location.
* Reorganizing an index uses minimal system resources.
* From scenario:
/ The index maintenance strategy for the UserActivity table must provide the optimal structure for both maintainability and query performance.
/ The CoffeeAnalytics database will combine imports of the POSTransaction and MobileLocation tables to create a UserActivity table for reports on the trends in activity. Queries against the UserActivity table will include aggregated calculations on all columns that are not used in filters or groupings.
/ When the daily maintenance finishes, micropayments that are one week old must be available for queries in UserActivity table but will be queried most frequently within their first week and will require support for in-memory queries for data within first week.
The maintenance of the UserActivity table must allow frequent maintenance on the day's most recent activities with minimal impact on the use of disk space and the resources available to queries. The processes that add data to the UserActivity table must be able to update data from any time period, even while maintenance is running.
* Columnstore indexes work well for mostly read-only queries that perform analysis on large data sets. Often, these are queries for data warehousing workloads. Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.