Many to many table - index design question

Hi
I have a Many-to-Many table , like
Author_ID int
Book_ID int
What is the best practices in designing the indexes for this table?
Of course the table should not be clustered, do you all agree on that?
So my options is a combined compound index on both fields. The
advantage
is that I get "is unique" automatically.
Or I create two separate indexes, one for each field.
What a bout primary keys? I guess I should set that on both? But not
making it
clustered?
What do you recommend that I do here?
//Andy
I would use both columns in the primary key, and yes I would cluster
it. And I would use both columns in the reverse order in a
non-clustered unique index, so it would be indexed both ways.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Feb 2008 07:43:51 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:

>Hi
>I have a Many-to-Many table , like
>Author_ID int
>Book_ID int
>What is the best practices in designing the indexes for this table?
>Of course the table should not be clustered, do you all agree on that?
>So my options is a combined compound index on both fields. The
>advantage
>is that I get "is unique" automatically.
>Or I create two separate indexes, one for each field.
>What a bout primary keys? I guess I should set that on both? But not
>making it
>clustered?
>What do you recommend that I do here?
>//Andy
|||Thanks for the reply.
Why would you want to have a clustered index on a many-to-many column?
I mean there's nothing sequencial, increasing and
stuff are added and deleted in random order. I guess inserts will take
quite some time if the DB has to phyiscally re-order the
data? Or am I wrong?
Why is it better to do clustering in this case?
//andy
|||On Thu, 21 Feb 2008 14:37:46 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:

>Thanks for the reply.
>Why would you want to have a clustered index on a many-to-many column?
>I mean there's nothing sequencial, increasing and
>stuff are added and deleted in random order. I guess inserts will take
>quite some time if the DB has to phyiscally re-order the
>data? Or am I wrong?
>Why is it better to do clustering in this case?
>//andy
I want two indexes, and both indexes "cover" all columns in the table.
If neither index is clustered there will be the table in a heap, a
complete copy of the table in the first index, and a complete copy of
the table in the second index. Three copies. If we cluster one of
the indexes, the leaf level is the base table, so we only have two
complete copies.
Roy Harvey
Beacon Falls, CT
|||Generally, but not always, tables with clustered indexes perform better than
heaps (the term for a table without a clustered index).
It is true that doing an insert that inserts a row that row needs to go into
a page that was full, the that page will have to be split, but while that
costs something, it's normally not that bad. I'm not quite sure what you
mean by "physically reorder the data", but I suspect you are thinking that
something goes on that is very drastic and that's not true.
You may want to read
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx
for a case study of a comparison of performance results with and without a
clustered index.
Of course, as always, "your mileage may vary". And it is possible that with
your data and your particular combination os selects, updates, inserts, and
deletes the heap will be faster. The only way to know is for you to test it
with your data. But my guess is that you will find either the clustered
index is faster, or there is no significant difference in performance.
If it were my system, unless I had some a priori reason to believe this
table was going to be a major performance bottleneck in my system, I would
just use a clustered index since that should usually turn out to be the
correct choice. If it turned out to be a performance problem, then I would
would run tests to see if I either made the wrong choice of keys for the
clustered index and/or if a heap was faster.
Tom
"Sune42" <sune42@.hotmail.com> wrote in message
news:9e5429c5-1f73-4a0b-9b70-6a9cff80803a@.c33g2000hsd.googlegroups.com...
> Thanks for the reply.
> Why would you want to have a clustered index on a many-to-many column?
> I mean there's nothing sequencial, increasing and
> stuff are added and deleted in random order. I guess inserts will take
> quite some time if the DB has to phyiscally re-order the
> data? Or am I wrong?
> Why is it better to do clustering in this case?
> //andy

Many to many table - index design question

Hi
I have a Many-to-Many table , like
Author_ID int
Book_ID int
What is the best practices in designing the indexes for this table?
Of course the table should not be clustered, do you all agree on that?
So my options is a combined compound index on both fields. The
advantage
is that I get "is unique" automatically.
Or I create two separate indexes, one for each field.
What a bout primary keys? I guess I should set that on both? But not
making it
clustered?
What do you recommend that I do here?
//AndyI would use both columns in the primary key, and yes I would cluster
it. And I would use both columns in the reverse order in a
non-clustered unique index, so it would be indexed both ways.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Feb 2008 07:43:51 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:
>Hi
>I have a Many-to-Many table , like
>Author_ID int
>Book_ID int
>What is the best practices in designing the indexes for this table?
>Of course the table should not be clustered, do you all agree on that?
>So my options is a combined compound index on both fields. The
>advantage
>is that I get "is unique" automatically.
>Or I create two separate indexes, one for each field.
>What a bout primary keys? I guess I should set that on both? But not
>making it
>clustered?
>What do you recommend that I do here?
>//Andy|||Thanks for the reply.
Why would you want to have a clustered index on a many-to-many column?
I mean there's nothing sequencial, increasing and
stuff are added and deleted in random order. I guess inserts will take
quite some time if the DB has to phyiscally re-order the
data? Or am I wrong?
Why is it better to do clustering in this case?
//andy|||On Thu, 21 Feb 2008 14:37:46 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:
>Thanks for the reply.
>Why would you want to have a clustered index on a many-to-many column?
>I mean there's nothing sequencial, increasing and
>stuff are added and deleted in random order. I guess inserts will take
>quite some time if the DB has to phyiscally re-order the
>data? Or am I wrong?
>Why is it better to do clustering in this case?
>//andy
I want two indexes, and both indexes "cover" all columns in the table.
If neither index is clustered there will be the table in a heap, a
complete copy of the table in the first index, and a complete copy of
the table in the second index. Three copies. If we cluster one of
the indexes, the leaf level is the base table, so we only have two
complete copies.
Roy Harvey
Beacon Falls, CT|||Generally, but not always, tables with clustered indexes perform better than
heaps (the term for a table without a clustered index).
It is true that doing an insert that inserts a row that row needs to go into
a page that was full, the that page will have to be split, but while that
costs something, it's normally not that bad. I'm not quite sure what you
mean by "physically reorder the data", but I suspect you are thinking that
something goes on that is very drastic and that's not true.
You may want to read
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx
for a case study of a comparison of performance results with and without a
clustered index.
Of course, as always, "your mileage may vary". And it is possible that with
your data and your particular combination os selects, updates, inserts, and
deletes the heap will be faster. The only way to know is for you to test it
with your data. But my guess is that you will find either the clustered
index is faster, or there is no significant difference in performance.
If it were my system, unless I had some a priori reason to believe this
table was going to be a major performance bottleneck in my system, I would
just use a clustered index since that should usually turn out to be the
correct choice. If it turned out to be a performance problem, then I would
would run tests to see if I either made the wrong choice of keys for the
clustered index and/or if a heap was faster.
Tom
"Sune42" <sune42@.hotmail.com> wrote in message
news:9e5429c5-1f73-4a0b-9b70-6a9cff80803a@.c33g2000hsd.googlegroups.com...
> Thanks for the reply.
> Why would you want to have a clustered index on a many-to-many column?
> I mean there's nothing sequencial, increasing and
> stuff are added and deleted in random order. I guess inserts will take
> quite some time if the DB has to phyiscally re-order the
> data? Or am I wrong?
> Why is it better to do clustering in this case?
> //andy

manual for microsoft course 2071 (sql queries)

Hello,
I am trying to get the manual for the above course.
Can anybody help me?
Chris
You should be able to buy this from any MS Learning Center (used to be called CTEC).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lalala" <floatingonthebreeze@.hotmail.com> wrote in message news:captfa$j18$1@.news.storm.ca...
> Hello,
> I am trying to get the manual for the above course.
> Can anybody help me?
> Chris
>

manual for microsoft course 2071 (sql queries)

Hello,
I am trying to get the manual for the above course.
Can anybody help me?
ChrisYou should be able to buy this from any MS Learning Center (used to be calle
d CTEC).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lalala" <floatingonthebreeze@.hotmail.com> wrote in message news:captfa$j18$1@.news.storm.ca.
.
> Hello,
> I am trying to get the manual for the above course.
> Can anybody help me?
> Chris
>

manual for microsoft course 2071 (sql queries)

Hello,
I am trying to get the manual for the above course.
Can anybody help me?
ChrisYou should be able to buy this from any MS Learning Center (used to be called CTEC).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lalala" <floatingonthebreeze@.hotmail.com> wrote in message news:captfa$j18$1@.news.storm.ca...
> Hello,
> I am trying to get the manual for the above course.
> Can anybody help me?
> Chris
>

Manipulating dates in an expression

Does anyone know a way to subtract a date from a date
parameter (e.g. = Parameters!Date.Value - 1, which of
course does not work)
thanksYou might want to read the MSDN documentation for the DateTime class:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
E.g. subtracting one day would be =Parameters!Date.Value.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks|||Here is how I subtracted one date from another to get a number of days
between them:
=Fields!DESIRED_SHIP_DATE.Value.ToOADate() -
Fields!ORDER_DATE.Value.ToOADate()
This information below is not a direct solution for your problem, but the
information may help you with other things data/time related.
Here is how I get the current hour and manipate it:
The DateAndTime.Hour(Now) return a 24 hour clock output for the hour, so
this code turns hour 13 to 1 (like 1pm).
Dim cHour As Integer = DateAndTime.Hour(Now)
If cHour > 12 Then
StartHourBox.Text = cHour - 12
Else
StartHourBox.Text = cHour
End If
Look at all the different options available to you with "DateAndTime",
including Day, Month, and year.
Later,
Ed Hammond
--
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks