469,111 Members | 1,964 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

composite primary key

i have a master table with around 15 columns and i am trying to find
the appropriate primary keys and indexes for the table.

To make the records unique, i need to include two datetime columns (
start and end dates ) and two integer columns ( attributes of the
record ) to make up a composite primary key. Both of these four
columns are found in the WHERE clause of my queries.

Is it acceptable in the view of performance and how should i create
the indexes?
Jul 20 '05 #1
5 14806
Hi

I think this is probably a candidate for a surrogate key.

John
"onder" <ok****@hotmail.com> wrote in message
news:9c**************************@posting.google.c om...
i have a master table with around 15 columns and i am trying to find
the appropriate primary keys and indexes for the table.

To make the records unique, i need to include two datetime columns (
start and end dates ) and two integer columns ( attributes of the
record ) to make up a composite primary key. Both of these four
columns are found in the WHERE clause of my queries.

Is it acceptable in the view of performance and how should i create
the indexes?

Jul 20 '05 #2
Hi John,

sorry for this but can you tell a little more on that?
Jul 20 '05 #3
Hi

There is usually quite a debate in the newsgroups when surrogate keys are
mentioned. Basically it is when you add a column that you populate with
unique values, so you can reference a row by that instead of the 4 columns
you mentioned. This is a way to reduce the width of any table that refers to
your table with a foreign key (you would need all 4 columns replicated in
that table if you did not use a surrogate key). If you searched google for
"Surrogate SQLServer" it will turn up a few debates.

Quite often a surrogate key is given the IDENTITY property, so SQL Server
will populate the values for you,
http://msdn.microsoft.com/library/de...asp?frame=true
but it is possible to maintain the values your self.

HTH

John
"onder" <ok****@hotmail.com> wrote in message
news:9c**************************@posting.google.c om...
Hi John,

sorry for this but can you tell a little more on that?

Jul 20 '05 #4
Ok about the surrogate keys. But i have an issue to ask:

In my 'select' queries, i always have the three columns which defines
a unique combination. So, i think the optimization of the table design
should bear this in mind.
If i add a surrogate key to the table to be a primary key, then i will
need to get that column to my select queries and then do the updates
based on that column.

What would you recommend for the index of the table?
Jul 20 '05 #5
Hi

You would need to have the surrogate key in the JOIN or WHERE clause. This
could be totally transparent to the user. As a Primary Key there will
already be an index on the surrogate key column,

It is possible a covering index on the surrogate and candidate key columns
will be useful, but like any index you should investigate if it is being
used and if the speed improvements are worth while.

John
"onder" <ok****@hotmail.com> wrote in message
news:9c**************************@posting.google.c om...
Ok about the surrogate keys. But i have an issue to ask:

In my 'select' queries, i always have the three columns which defines
a unique combination. So, i think the optimization of the table design
should bear this in mind.
If i add a surrogate key to the table to be a primary key, then i will
need to get that column to my select queries and then do the updates
based on that column.

What would you recommend for the index of the table?

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Thomas A. Anderson | last post: by
4 posts views Thread by Ismail Rajput | last post: by
2 posts views Thread by Peter Lindquist | last post: by
7 posts views Thread by Ronald S. Cook | last post: by
2 posts views Thread by lfhenry | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.