Question 5) SQL Server 2005 Implementation

SubObjective: Implement Indexes

Single Answer Multiple Choice

You are a database administrator for a software development firm named Esoft Inc. The company maintains all its data on a SQL Server 2005 database named Esoft_Data01. The database consists of a major table named Employee_details that stores the details of employees in the company. When the table was initially created the data in the table was to be queried on the Employee_id column. Therefore, a primary key was created on the Employee_id column. After the database was brought into production, the requirement changed, and the table was primarily queried on the Department_id column.

What should you do to improve the performance of the queries by using the Department_id column?

A. Create a non-clustered index on the Department_id column.
B. Create a clustered index on the Department_id column.
C. Rebuild the clustered index on the Employee_id column.
D. Use a stored procedure to access the data in the Employee_details table.
E. Create a clustered index on the Department_id column after dropping the clustered index on the Employee_id column.

Answer:
A. Create a non-clustered index on the Department_id column.

Tutorial:
The option stating that you should create a non-clustered index on the Department_id column is correct. This is because a non-clustered index has the same B-tree structure as a clustered index. To improve the performance of queries that use the Department_id column, you can create an index on the column. In this scenario, a clustered index already exists on the Employee_id column. Therefore, you should create a non-clustered index on the column. A non-clustered index will increase the performance of the query because the SQL Server engine will scan only the index page containing the non-clustered index on the Department_id column. This will reduce the time required to retrieve data from the database.

The option stating that you should create a clustered index on the Department_id column is incorrect because you cannot create two clustered indexes on a table. Only one clustered index can exist in a table.

The option stating that you should rebuild the clustered index on the Employee_id column is incorrect. This is because rebuilding the clustered index on the Employee_id column will not affect the performance of the queries that use the Department_id column. Rebuilding the Employee_id column can improve the performance of the queries that use the Employee_id column in the WHERE clause.

The option stating that you should use a stored procedure to access the data in the Employee_details table is incorrect. You will not be able to improve the performance of the query by using a stored procedure to access the data in the table. This is because a stored procedure will also use a T-SQL query to access the data in the table. To do this, the SQL Server engine will use the clustered index that exists on the Employee_id column.

The option stating that you should create a clustered index on the Department_id column after dropping the clustered index on the Employee_id column is incorrect. Dropping the clustered index on the Employee_id column will adversely affect the performance of the queries that use the Employee_id column in the WHERE clause.

Reference:
MSDN, Search, “Enterprise Servers and Development,” “SQL Server,” “SQL Server 2005 Documentation,” “SQL Server 2005 Books Online,” “SQL Server Database Engine,” “Designing and Creating Databases,” “Indexes,” “Implementing Indexes,” “Creating Indexes (Database Engine).”

MSDN, Search, “Enterprise Servers and Development,” “SQL Server,” “SQL Server 2005 Documentation,” “SQL Server 2005 Books Online,” “SQL Server Database Engine,” “Designing and Creating Databases,” “Indexes,” “Implementing Indexes,” “Creating Indexes (Database Engine),” “Creating Nonclustered Indexes.”

These questions are derived from the Self Test Software Practice Test for Microsoft exam 70-431-TS: Microsoft SQL Server 2005 Implementation and Maintenance