Microsoft TS:MS SQL Server 2008,Implementation and Maintenance Sample Questions:

1. 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 alter the query hint to force the optimizer to force a unclustered index seek.
B) You should reproduce the whole indexes on the VideoTitle table.
C) You should delete the query hint from the query.
D) You should Re-create the index on only the ReleaseDate column.

2. You administer a SQL Server 2008 instance that runs on a Windows Server 2003 computer. The instance uses mixed authentication mode.
You need to ensure that the SQL Server 2008 authenticated logins follow the same password complexity rules that are enforced by Windows Server 2003. You also need to ensure that the password complexity rules continue to be enforced.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A) Use the ALTER LOGIN ... CHECK_EXPIRATION = ON statement to modify all logins.
B) Create an SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected.
C) Use Policy-Based Management to create a policy that prevents any violation of these rules.
D) Use the ALTER LOGIN ... HASHED statement to modify all logins.
E) Use the ALTER LOGIN ... CHECK_POLICY = ON statement to modify all logins.

3. You administer a SQL Server 2008 instance that contains a database named DB1. DB1 contains a table named Table1.
The DB1 database includes a stored procedure named Procedure1. Procedure 1 uses a sp_executesql Transact-SQL statement to select data from Table1.
According to business requirements, users are not allowed to access tables directly in any database.
When a user executes Procedure1, the following exception is raised:
"Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object Table1', database 'DB1', schema 'dbo'."
You need to ensure that the user can successfully execute Procedure1 without violating the business requirements.
What should you do?

A) Execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.
B) Alter Procedure1 and add the EXECUTE AS USER = 'dbo' option immediately before the call to the sp_executesql stored procedure.
C) Execute the GRANT SELECT ON dbo.Table1 TO User1 Transact-SQL statement.
D) Alter Procedure1 and add the WITH EXECUTE AS OWNER option to its header.

4. You have a server that contains a default SQL Server 2005 instance. You need to install a SQL Server 2008 instance for a new application on the same server. The new application requires SQL Server 2008 functionality.
You need to ensure that both database instances are available for their respective certified third-party applications. The existing application environments remain unchanged.
What should you do?

A) Upgrade the SQL Server 2005 application to use SQL Server 2008.
B) Install SQL Server 2008 as a named instance.
C) Upgrade the SQL Server 2005 instance to a SQL Server 2008 instance.
D) Install SQL Server 2008 as the default instance.

5. You administer a SQL Server 2008 instance. The server contains a very large database that is used by an application that is constantly available.
Users report that the server performance has degraded.
You need to use the Database Engine Tuning Advisor to improve the performance of the application.
You also need to ensure that the performance of the production server is unaffected while analyzing the workload.
What should you do?

A) Enable the XP_MSVER stored procedure on the local server.
B) Configure a test server that has a similar hardware configuration. Use the dta.exe utility on the test server along with an XML input file.
C) Use the dta.exe utility on the production server along with an XML input file.
D) Enable the XP_MSVER stored procedure on the remote server.

Solutions: