July 25, 2004 at 11:22 pm
Hi,
I have an intersection table with 3 foreign keys(two integer columns and the other is char(10)). I mostly Query this table using all/one/two of these columns and these columns are used in joins. I created a primary key column with identity value.Later I deleted it and created a composit primary key with 3 foreign key columns. I could not find any change in query execution time. I have tried this with 20,000 records, the application is still in development stage.
I could not decide that whether to use composit primry key or identity column.
Pl suggest me which one is good.
D.Ravipati
July 26, 2004 at 12:00 am
If there is chance that this table will be reffered by other tables, I would use the identity column as the primary key; if you are sure that you will never need to reffer this table, you could use the composite primary key.
If you use the identity primary key, make sure that you also have a UNIQUE constraint on those three columns. This is important, to make sure that you don't have duplicate data in table.
You could also make an index on each of the three columns, to improve the efficiency of some joins.
To see more details about the cost of executing a query, you can use:
a) Show Execution Plan in Query Analyzer
b) Profiler (look at the following columns: CPU, Reads, Writes, Duration)
Razvan
July 26, 2004 at 1:50 am
I tend to follow the practice that if the table is in the middle of a query then I need a single unique field to act as a primary key.
If the table is at the bottom of a query then I am happy to use a composite primary key, but there is a big proviso.
The primary key (especially a clustered primary key) must not be editable. If you have a compound primary key make sure you do not run the risk of your primary key value changing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply