Table's design

Question

Hi everybody,

I'm trying to figure out why one of the tables I took a quick look today is designed this way and does it make any sense or not.

The table currently has ~42 mln rows. It has bigint identity column defined as primary key (non clustered) and it also has varchar(38) column which has a clustered index for it (the max length of this column is currently 18 and that column is not unique).

Does it really make any sense to have the clustered index on that varchar(38) column instead of the bigint identity unique column?

Also, that table is a temporal table. Assuming that we would need to change the index, is it possible to estimate how long the re-index process will take and what would be the correct way to swap the clustered vs non clustered?

The other question is simpler - I noticed that the names of the constraints use the wrong names (it looks like that table originally was named differently) - is it a simple matter of just renaming the constraints?

Thanks in advance.

Answer 1

It could very well be that the chosen column for the clustered index is correct and valid. It all depends on how the table is queried - for example, if the majority of the queries use that column in the where clause to identify the row to be returned - or that column is used in a range for the majority of queries. A good example is a datetime column as the clustered index - when most queries use a date range (e.g. last months data, last weeks, yesterdays, etc.).

Unless you are seeing performance issues on most of the queries accessing this table - I wouldn't be looking to change it, especially if the only reason is because you want the PK to also be the clustered index. It doesn't have to be and often that is not a good practice.