471,893 Members | 1,556 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,893 software developers and data experts.

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 14876
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
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.