Guys,

Here is the scenario:

SQL Server 2000 (8.0.2055)

Table currently has 478 million rows of data. The Primary Key column is an INT with IDENTITY. There is an Unique Constraint imposed on two other columns with a Non-Clustered Index. This is a vendor application and we are only responsible for maintaining the DB.

Now the vendor has recommended doing the following "to improve performance"

1. Drop the PK and Clustered Index
2. Drop the non-clustered index on the two columns with the UNIQUE CONSTRAINT
3. Recreate the PK, with a NON-CLUSTERED index
4. Create a CLUSTERED index on the two columns with the UNIQUE CONSTRAINT

I am not convinced that this is the right thing to do. I have a number of concerns.

By dropping the PK and indexes, you will be creating a heap with 478 million rows of data. Then creating a CLUSTERED INDEX on two columns would be a really mammoth task. Would creating another table with the same structure and new indexing scheme and then copying the data over, dropping the old table and renaming the new one be a better approach?

I am also not sure how the stored procs will react. Will they continue using the cached execution plan, considering that they are not being explicitly recompiled.

I am simply not able to understand what kind of "performance improvement" this change will provide. I think that this will actually have the reverse effect.

All thoughts welcome.

Thanks in advance,

Raj