Study with Microsoft : 070-432 Exam Torrent as your best preparation materials

You are mastering the company database. During the development, you find that Transact-SQL query below is running slowly:SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN '20050401' AND '20050510' A clustered index exists on the VideoTitle column.
There is a nonclustered index on the ReleaseDate column which contains the UpcNum and RetailPrice columns. The result of the avg_fragmentation_in_percent is 30 percent, when you search the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table,In order to find this method to solve this problem, which is the correct answer?

• A. You should delete the query hint from the query.
• B. You should Re-create the index on only the ReleaseDate column.
• C. You should reproduce the whole indexes on the VideoTitle table.
• D. You should alter the query hint to force the optimizer to force a unclustered index seek.

You are managing a SQL Server 2008 computer called SQLTEST1. You should configure the SQL Server service and the SQL Server Agent service to start automatically, and set SQLTEST1 to audit all user names and application names that plan to get information from SQLTEST1. SQLTEST1 must operate auditing always, and you should keep the results for 10 years. Which is the correct answer?

• A. You should create a SQL Server Agent job to run the SQL Trace stored procedure. Configure the job to start when the SQL Server Agent service starts.
• B. You should disable the C2 Audit Tracing setting on the server properties.
• C. You should configure the server authentication mode as SQL Server and Windows Authentication mode.
• D. You should set the Login auditing setting to both failed and successful logins

You are managing a SQL Server 2008 instance which includes a database called TesterDB for Home.com. The data file and the transaction log file which are located on the D: drive that owns only 10 percent useful space. You should make sure that you move both files to the G: drive.
Which is the correct answer?

• A. You should implement the statement below: ALTER DATABASE Finance SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and
• B. mdf'); ALTER DATABASE Finance SET MULTI_USER;
• C. ALTER DATABASE TesterDB
• D. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE(NAME = Finance_Data, FILENAME =
• E. mdf'); ldf'); ALTER DATABASE TesterDB SET ONLINE;
• F. You should utilize the following Transact-SQL statement.
• G. ALTER DATABASE TesterDB SET OFFLINE WITH ROLLBACK_IMMEDIATE;

You administer a SQL Server 2008 instance that contains a database named Sales.
A SQL Server login named SalesAppLogin maps to a database user named SalesAppUser in the Sales database.
You need to ensure that the database user can perform only the following tasks:
- Execute all stored procedures that currently exist in the Sales database. - Execute all stored procedures that will be created in the Sales database.
What should you do?

• A. Grant the appropriate database-level permissions to SalesAppUser.
• B. Grant the appropriate server-level permissions to SalesAppLogin.
• C. Grant the appropriate object-level permissions to SalesAppUser.
• D. Add SalesAppUser to the appropriate fixed database roles.

You maintain a default SQL Server 2008 instance.
You plan to configure FILESTREAM data to meet the following requirements: - Enable FILESTREAM for file I/O streaming access. - Allow remote client computers to have streaming access to FILESTREAM data.
You need to ensure that FILESTREAM data is enabled.
Which service should you configure?

• A. SQL Server
• B. Distributed File System
• C. SQL Server Full Text
• D. SQL Server VSS Write