CODEWITHSUNDEEP
sql-serverindexingsql-server-2012
ulty4life
ulty4life

Reputation: 3012

Add Clustered Index to Table with Non-Clustered Unique PK Index

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:

Upvotes: 0

Views: 126

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

I'd say, that with 50 rows it doesn't really matter. I'd create a

  • clustered index (primary key) on (id1, id2)
  • plus non-clustered unique index on (id2, id1)

This will cover all possible queries.

Once in a while (once a day or week or after changes to this infrequently changing table) you can rebuild all indexes to defragment them and keep statistics up to date. This kind of maintenance should be done for all tables any way.

Upvotes: 2

Related Questions