Decide which column should have primay key so it has clustered index?

Question

Hi All,

I am new to sql. i have a small question. i have two tables as follows.

Table-A

Column-Key

87634799
98746323

Column-AwpUnitCost

2.3

4.0

Table-B

Column-Type
Missing

Invalid

Column-Destination

http://www.destination1.com

http://www.destination2.com

Table-A may have thousand records. AwpUnitCost may be positive,negative or Null.

There is no relation in these tables. if Table-A has records then they develop the reports and report type and report links are in table-B. Table-B has fixed two records as shown above. just wondering how can i decide which column should have primary key so it would have clustered index? do i have to add one more column in table-A like KeyId?

Appreciate any help.

Answer 1

Clustered index is a physical index. If you have an ascending clustered index on a field, then inside the file the data is stored in an ascending order of that field. Decide what you need, then decide which shall be the clustered index. As I remember correctly, the primary key is always a clustered index (correct me if I'm wrong), but, unlike the clustered index, the primary key requires all rows to have a distinct primary key - it should be unique. So if you need a primary key (for foreign keys definition, for transaction log-level replication) then choose a unique set of fields that you would like to be stored physically in an ascending order (for performance - consider both insertions and selects).

In Table A, if the key is unique, it would be a good candidate for a primary key, but if it is random it could slow down insertions.

For such reasons (not to give me too much of a headache), I use identity (autoincrement integer fields) for primary keys. Then, where needed, I add unique keys, indexes, etc. If your tables are related, add foreign keys to that identity field.