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.
These questions are derived from the Self Test Software Practice Test for Microsoft exam 70-431-TS: Microsoft SQL Server 2005 Implementation and Maintenance