I have an existing table with structure:
TableA
(
Id1 int not null,
Id2 int not null
)
Where the primary key is the composite (Id1, Id2). If you haven't deduced it yet, this is a many-to-many associative table. These are the only columns in the table.
The actual application data populating the table are only one-to-many relationships, due the nature of the business use case in this instance. The number of rows is quite small. Somewhere ~50 rows. New Id2 records occasionally get created and then associated to existing Id1 records. Even more rarely a new Id1 record will be created that requires inserting a new set of Id1, Id2 records. On a day-to-day basis however, the data is static. The table is heavily used in join queries.
The only index on the table is nonclustered, unique, primary key (created as part of the constraint definition) on (Id1, Id2).
To meet some requirements for synchronizing data to another database, I need to add a clustered index to this table.
What is the best way to do this while maintaining the best performance and good physical data organization?
Given the small number of rows, I'm leaning toward replacing the non-clustered index with a clustered index.
Some thoughts:
- Since there are no other columns in the table, the clustered index can't be added on any other columns.
- Adding a clustered index on only one column doesn't make sense and could be detrimental.
- Will it hurt to have both a clustered index and a non-clustered index on the same columns?
- Because the actual data is one-to-many and does not utilize the many-to-many structure, replacing the non-clustered index with a clustered index is not bad.
- Data inserts into a clustered index on the PK columns cause bad physical data organization.
- Adding an identity column to the table and putting the clustered index on it gets around the issue, but provides no benefit to querying at all.
- I'm probably over-analyzing this.
