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]

Re: composite primary key

Hi

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

John
"onder" <okeen1@hotmail .com> wrote in message
news:9c3d3756.0 309022355.10656 428@posting.goo gle.com...[color=blue]
> 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?[/color]

[onder]

Re: composite primary key

Hi John,

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

[John Bell]

Re: composite primary key

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/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3iex.asp?frame=true

but it is possible to maintain the values your self.

HTH

John


"onder" <okeen1@hotmail .com> wrote in message
news:9c3d3756.0 309030711.5f4f5 82c@posting.goo gle.com...[color=blue]
> Hi John,
>
> sorry for this but can you tell a little more on that?[/color]

[onder]

Re: composite primary key

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]

Re: composite primary key

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" <okeen1@hotmail .com> wrote in message
news:9c3d3756.0 309080039.65ccc c67@posting.goo gle.com...[color=blue]
> 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?[/color]