Showing posts with label appropriate. Show all posts
Showing posts with label appropriate. Show all posts

Monday, March 19, 2012

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?Hi

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

John
"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309022355.10656428@.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?|||Hi John,

sorry for this but can you tell a little more on that?|||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/d...asp?frame=true
but it is possible to maintain the values your self.

HTH

John

"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309030711.5f4f582c@.posting.google.c om...
> Hi John,
> sorry for this but can you tell a little more on that?|||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?|||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.0309080039.65cccc67@.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?

Saturday, February 25, 2012

Complex Order By Logic

I apologize if this is not the appropriate forum for this question.

I've

written a searchable database-driven application in classic ASP and

vbscript with a SQL Server backend. What I need to do is this: order

the results of a query so that if the first "order by" field is null to

order that entry based on the second "order by" field.

The

application I am writing is a database of books, and my client wants

the results to be ordered by Author, unless there is no Author, in

which case he wants that entry ordered by book title. Here is an

example of how he wants the books sorted:

Adamson, Jan - Book X
Bible, The
Wilson, Jonathan - Book Y

I

hope I've explained the scenario correctly. What's the best way to

write a SQL statement that will yield the ordering criteria described

above? Also, is there any way to get "Order by" to ignore articles like

"A" and "The?"

I personally hate this idea, as it would be annoying to scan through. I would put no authors at the end or beginning as 'No Author.'

You can use coalesce to do this:

create table authorTitle
(
author varchar(20),
title varchar(20)
)
insert into authorTitle
select 'Adamson, Jan','Book X'
union
select NULL, 'Bible, The'
union
select 'Wilson, Jonathan','Book Y'
go

select *
from authorTitle
order by coalesce(author,'') + coalesce(title,'')

author title
-- --
Adamson, Jan Book X
NULL Bible, The
Wilson, Jonathan Book Y

You will probably going to want to add this as a computed column, and likely index it to get this to get this to perform well if you have lots of books in the database.

|||Thank you! That did the trick.