composite primary key

[onder]

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?

[John Bell]

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?

[onder]

Hi John,

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

[John Bell]

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?

[onder]

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?

[John Bell]

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?