I have a question regarding database design.
Lets assume that there is a table with 10 columns. I don't have any single column which I can choose as candidate for Primary key. Though, combination of 5 columns can uniquely identify records.
Here, I created an identity column as a PK and added UNIQUE constraint on those 5 columns. I was thinking about creating non clustered index on these 5 columns.
These 5 columns are common to many tables and are ,hence, used while in JOIN & WHERE clause.
So, from performance perspective, it makes sense to have clustered index on columns which are widely used in Join clause. Joining on columns of clustered index is faster than that of non clustered index.
So, I am not being able to understand which option is better...
clustered index on PK column
Clustered Index on 5 columns.
I feel if I create a clustered Index on PK coulmn then I should also create a lookup table which contains
PK column values each corresponding to comnbination of values from those 5 columns.
In this way, pk column can also be used in joining. In such case, I will need lots of lookup table containing values for Identity key column(PK values) corresponding to combination of those 5 or more columns.
One more thing: Identity column is like surrogate column. It doesn't contains data which makes sense to any user instantly.