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 replication in sql server 2000

Hi

I'm trying to determine if it is possible to do many to many replication in sql server 2000.

What i basically want is to have n databases share the same basedate (share a common database) and allow updates in any database to be replicated to all the other databases (with a simple conflict resolution, like last update wins).

My goal is total autonomy, without a single point of failure. If any node goes down, the other nodes will continue to work and continue to replicate their data to the remaining nodes. When a node comes back up it will catch up with the over nodes (or get reinitialized if it was a serious crash).

The amount of data i want to replicate is not that big (less than 100MB) and does not change that often. All servers are sql server 2000 instances connected by a gigabit network and the number of nodes involved is less than 10. Some latency is also acceptable.

the question is: is this at all possible? I have read i bit in 'SQL Server High Availability By Paul Bertucci' and some other resources and it looks like a multiple publishers or multiple subscribers with merge replication setup should work, but i'm not too sure if it will work for n > 2 nodes (where all nodes publish and subscribe to each other) and it also mentions constraints on which data a given node is allowed to update (i hope this could be handled by simple conflict resolution).

And if it is not possible in 2000, could it be accomplished en 2005?

TIA Jens

Unfortunately you cannot do peer to peer replication you must have a publisher / subscriber model. You can have a cluster of servers at the center for availablilty in SQL 2005 i believe.

However one server (or cluster of servers) needs to be the master (publisher) and all the others subscribers.

Martin

|||

Martin_McNally wrote:

Unfortunately you cannot do peer to peer replication you must have a publisher / subscriber model. You can have a cluster of servers at the center for availablilty in SQL 2005 i believe.

However one server (or cluster of servers) needs to be the master (publisher) and all the others subscribers.

Martin

This was what i thought, but in the book i mention there is examples of a Multiple Publishers/Multiple Subscribers setups, where each node is both subscriber, publisher and distributer, to quote from the book (full book and figures available here):

Microsoft? SQL Server High Availability By Paul Bertucci wrote:

In the multiple publishers or multiple subscribers scenario, as shown in Figure 7.15, a common table (such as the Customers table) is maintained on every server participating in the scenario. Each server publishes a particular set of rows that pertain to it—usually via filtering on something that identifies that site to the data rows it owns—and subscribes to the rows that all the other servers are publishing. The result is that each server has all the data at all times, and can make changes to its data only. You must be careful when implementing this scenario to ensure that all sites remain synchronized. The most frequently used applications of this configuration are regional order processing systems and reservation tracking systems. When setting up this configuration, make sure that only local users update local data. This check can be implemented through the use of stored procedures, restrictive views, or a check constraint.

But i guess setting up multiple servers as both subscriber, publisher and distributer and letting them all subscribe to each other will cause havoc without the above mentioned constraints.

But i guess i'm not the first person to want this kind of setup and if it was possible i would have found some references somewhere on the net (i just did another search and found this article which describes what i'm looking for, but it is only for 2005 and when looking further into it, it does not support conflict resolution, which i think is required, guess we will have to build it ourselves if we really want it).

Best regards Jens

Manual back of database error

Hi

I am attempting to take a manual backup of an SQL 2005 database, via

Right Click on MyDatabase --Tasks --Backup
[Specify the filename path and type of backup]

However, when I try to specify the filename or, in fact, do anything, I
receive the following error

Property BackupDirectory is not available for Settings
'Microsoft.SqlServer.Management.Smo.Settings'. This property may not
exist for this object, or may not be retrievable due to insufficient
access rights. (Microsoft.SqlServer.Smo)

The user I'm logged into the database with, however, has full rights
within SQL.

Does anyone have any ideas?

Thanks!<smokejo@.googlemail.comwrote in message
news:1164142996.477637.282180@.f16g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

Hi
>
I am attempting to take a manual backup of an SQL 2005 database, via
>
Right Click on MyDatabase --Tasks --Backup
[Specify the filename path and type of backup]
>
However, when I try to specify the filename or, in fact, do anything, I
receive the following error
>
Property BackupDirectory is not available for Settings
'Microsoft.SqlServer.Management.Smo.Settings'. This property may not
exist for this object, or may not be retrievable due to insufficient
access rights. (Microsoft.SqlServer.Smo)
>
The user I'm logged into the database with, however, has full rights
within SQL.

Sounds like the user doesn't have full rights on the directory you're trying
to back up to.

Quote:

Originally Posted by

>
Does anyone have any ideas?
>
Thanks!
>

Manipulation in the front end

Hi

I have a scenario while creating a report where I need to maintain the same number of rows for a column, even if the data grows or shrinks for that column

example

Each Scenario is grouped by ID

Scenario 1

columnA

Row1 X X

Row2 X

Row3 X

Row4 XYZ

Scenario 2

columnA

Row1 XA

Row2 XYZ

In both scenario's I need to control from front end

the number of row should remain 4

I am grouping by row field say Type

This field Type varies for each ID group

This seems to work.

1. Use a table with a group on the ID field.

2. Use four rows in the detail section

3. On the first detail row Hidden property use this expression.

=iif(RowNumber("table1_Group1")<=4,False,True)

4. On the second detail row Hidden property use this expression.

=iif(CountRows()=1,False,True)

5. On the third detail row Hidden property use this expression.

=iif(CountRows()=1 or (CountRows()=2 and RowNumber("table1_Group1")=2) ,False,True)

6. On the third detail row Hidden property use this expression.

=iif(CountRows()=1 or (Countrows()=2 and and RowNumber("table1_Group1")=2) or (Countrows()=3 and and RowNumber("table1_Group1")=3) ,False,True)

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated
"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA
|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Manipulating dates

Hi
I have a field called paid to date and need to calculate the next date with
the same day of the month from getdate(). For example, paid to date of
13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/2005
.
However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
Any help in achieving this would be greatly appreciated"Dene" <Dene@.discussions.microsoft.com> wrote in message
news:7C91B3BF-738D-4842-BD64-33863B2EC559@.microsoft.com...
> Hi
> I have a field called paid to date and need to calculate the next date
> with
> the same day of the month from getdate(). For example, paid to date of
> 13/02/2005 should give a next date of 13/02/2005 where getdate is
> 10/02/2005.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
> Any help in achieving this would be greatly appreciated
Can you post some DDL and SQL for what you are currently doing and what you
are trying to achieve.
I can't tell from your question what it is that you are after.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:

>Hi
>I have a field called paid to date and need to calculate the next date with
>the same day of the month from getdate(). For example, paid to date of
>13/02/2005 should give a next date of 13/02/2005 where getdate is 10/02/200
5.
> However, 13/02/2005 should return 13/03/2005 where getdate is 15/02/2005.
>Any help in achieving this would be greatly appreciated
Hi Dene,
Is this what you are after?
declare @.basedate smalldatetime
declare @.now smalldatetime
set @.basedate = '20050213'
set @.now = '20050210'
SELECT DATEADD(month,
DATEDIFF(month, @.basedate, @.now)
+ CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
@.basedate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
This looks exactly what I'm after
Reards
Dene
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 10:29:04 -0800, Dene wrote:
>
> Hi Dene,
> Is this what you are after?
> declare @.basedate smalldatetime
> declare @.now smalldatetime
> set @.basedate = '20050213'
> set @.now = '20050210'
> SELECT DATEADD(month,
> DATEDIFF(month, @.basedate, @.now)
> + CASE WHEN DAY(@.basedate) < DAY(@.now) THEN 1 ELSE 0 END,
> @.basedate)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Managing database growth

Hi
I've a DB in SQL Server 2000 which allows the
database to grow by a percentage (defualt=10%).
Now,anfter any action(for example insert ing a row)
does it calculate the DB size and allocate the
accurrate size of space by defined percentage?
does it take long time and has overhead?
is it a good way to define database grow by
percentage?
Thanks for any help.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/> Now,anfter any action(for example insert ing a row)

quote:

---

> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?

---

Yes

quote:

---

> does it take long time and has overhead?

---

No

quote:

---

> is it a good way to define database grow by
> percentage?

---

Yes
Otherwise, when the database grow and somebody forget to give more space, it
will happen some ugly error.
I think if the database it some, you should let the database grow by fix
size, otherwise grow by percentage|||SQL Server doesn't do the grow after an insert. It happens whenever SQL Serv
er need space for an
insert, update etc and the file is full. The user is blocked by the grow ope
ration during the grow.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...

quote:

---

[c
olor=darkred]
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/[/color]

---

|||RM,
My personal preference is for a fixed growth unit.
Say, blocks of 1024MB for data and 512MB for log.
For some databases, excessive numbers of small growth will give you
fragmentation on the physical files.
I have seen some databases with the primary file in 300+ fragments. This
cannot be good.
Much better is a scheme something like this:
Initial size: 30GB Data / 3GB Log
Data growth: 3072MB
Log growth: 1024MB
This will minimize the number of 'expands' that need to run.
Planning is key here.
What you want to avoid is the 'ever-larger' growth syndrome.
I.e. Initial Size 30GB Data / 3GB Log.
Data growth: 10%.
Log growth 10%.
This percentage grows ever larger each time the DB expands.
Again, this is just personal preference.
James Hokes
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr2g3ovl8hqligo@.msnews.microsoft.com...

quote:

---

> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

---

Managing Big Database

Hi
I have a Database over 50G. It increases 1G per day!
Today I detach the current database and move the data&log file to E drive. T
hen I use DTS transfering those data to a new database on D drive. The data
base becomes 18G!!!! I check the tables, everything is there.
How did this happen? I thought there must be something about fragments. But
I am not sure. Can anyone tell me?
And is there any other way to defrag the database without taking off-line?
thank you very muchDBCC ShrinkDatabase?
Most of the suggestions in http://www.aspfaq.com/2446 apply to any database.
Also see http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...

quote:

---

> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.

---

Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.

quote:

---

> How did this happen? I thought there must be something about fragments.

---

But I am not sure. Can anyone tell me?

quote:

---

> And is there any other way to defrag the database without taking off-line?
>
> thank you very much

---

|||I have found the cause! It's because that I didn't build the index.|||I shrink database every day. The log file is quite small.|||and make sure you take backups of the database in order to be able to shrink
the transaction log in case of a full recovery model.
Check books online on dbcc shrinkdatabase, dbcc shrinkfile, and shrinking
the transaction log
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...

quote:

---

> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.

---

Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.

quote:

---

> How did this happen? I thought there must be something about fragments.

---

But I am not sure. Can anyone tell me?

quote:

---

> And is there any other way to defrag the database without taking off-line?
>
> thank you very much

---