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

1. You are managing a SQL Server 2000 instance which includes a database called Products for Home.com. You decide to install a SQL Server 2008 instance for business. You should
make sure that third-party applications could run normally on both database instances, you could get the results by conforming to the listed conditions:1. The existed application environments keep common. 2. Minimal database administrative effort.Which is the correct answer?

A) Before setting the new application to utilize the new instance. You could install SQL Server 2008 as a named instance
B) Before setting the new application to utilize the default instance. You could install SQL Server 2008 as a named instance
C) You should update the SQL Server 2000 application to utilize SQL Server 2008.
D) You should update the SQL Server 2000 instance application to utilize SQL Server 2008 instance.

2. You administer a SQL Server 2008 instance. You need to allow users on instance to query remote data sources by using the OPENROWSET() sp_configure configuration option should you configure?

A) Ad Hoc Distributed Queries
B) remote proc trans
C) Agent XPs
D) remote access

3. You plan to install a SQL Server 2008 instance for a new application on an existing server that contains a default SQL Server 2005 instance.
You need to ensure that both database instances are available for their applications. Your solution must meet the following requirements:
- Minimize administrative effort.
- Prevent changes to the existing application environments.
What should you do?

A) Configure the new application to use a SQL Server 2008 named instance.
B) Configure both instances to use SQL Server 2008.
C) Configure the new application to use the SQL Server 2008 default instance.
D) Configure both applications to use SQL Server 2008.

4. You are mastering the company database; you should use the SQL Server 2005 database which enables business users to operate both ad hoc and predefined searches. Youdoubt that some searched will cost too many resources. You should clarify which searches cost the most resources. You decide to finish this aim as quickly as possible. Which is the correct answer?

A) You should utilize the SHOWPLAN session selection.
B) You should utilize the DBCC INPUTBUFFER requirements
C) You should utilize the sqldiag function
D) You should utilize the sys.dm_exec_query_stats dynamic management view (DMV).

5. You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.
The table has the following definition: Currently, the table is partitioned by year with each year in its own filegroup.

You need to add a new partition for the upcoming year.
What should you do?

A) Create a new filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
Use ALTER INDEX REORGANIZE statement.
B) Remove the clustered index from the table.
C) Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItem3',
@OptionName= 'PartltionByYear';
@OptionValue= 'true';
D) Execute the DBCC CLEANTABLEcommand on the OrderItems table.
E) Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
F) Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE
clause.
G) Create a new Filegroup.
Create a new database File.
Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
H) Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
I) Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO (SECONDARY);
J) Use the ALTER TABLEstatement to remove the COLLATEoption.

Solutions: