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

Manuel Identity system

Hi,
I have a table (TABLE_A) like this:

MY_ID int unique
Field1 varchar(10)

I am using manuel identity system like this:
INSERT INTO TABLE_A (MY_ID, Field1) SELECT MAX(MY_ID) + 1, 'Field1 Value'

One row insert there is no problem, but more than one row how can I use insert statement?
(TABLE_B have more than one record)

INSERT INTO TABLE_A (MY_ID, Field1)
SELECT (SELECT MAX(MY_ID) + 1 FROM TABLE_A), FieldX FROM TABLE_B

Iron:

I normally use a stored procedure for retrieving identity values from identity tables. In this way I can have the procedure retrieve single identifiers or a list of identifiers and maintain the identity table.

|||

Hi,

Why not using the identity in table_A like this:

create table A

(

My_ID int identity(1,1)

Field1 varchar(10)

)

any way you intend to insert the max id each time - here in this way every time you will make an insert the

table A will get an id automatically.

so if you want to insert to table A field from table B you can do the following:

insert into table A

select Field from table B

when you will do that each time your new record will get a new max id.

i.e:

insert into table A

select 'test' from B

union

select 'test1' from B

your table A will look like the following:

1 test

2 test1

|||

Something that may or may not matter to you is that using IDENTITY to create incremental values can result in range gaps if an INSERT is rolled back for whatever reason. If you cannot tolerate gaps in your range of values then stick with a custom generation method. If gaps are acceptable then why not consider using IDENTITY?

To create incrementing values for several rows at once you could use SQL Server 2005's ROW_NUMBER() function added to an offset obtained from a seed table. You could then take the existing seed value, add the value of @.@.ROWCOUNT then store the new value in the seed table. Note that if using @.@.ROWCOUNT then you will need to be careful if you have triggers on the table as these could cause @.@.ROWCOUNT not to reflect the number of rows inserted into the base table. You also need to be careful if you are using SNAPSHOT isolation or if you are using the READ UNCOMMITTED isolation level. I've included an example of this method below.

Chris

/*

--Create and populate the test tables.

CREATE TABLE dbo.TestTable

(

TestTableID INT NOT NULL PRIMARY KEY,

TestValue VARCHAR(8000) NOT NULL

)

GO

CREATE TABLE dbo.TestTableSeed

(

SeedValue INT NOT NULL

)

GO

--Populate the seed table - arbitrary number for demo purposes.

INSERT INTO dbo.TestTableSeed(SeedValue)

VALUES(425)

GO

*/

--Repeatedly run this batch to insert new rows into dbo.TestTable and

--increment the seed to the most recent value inserted into the TestTableID column.

DECLARE @.SeedValue INT

DECLARE @.SeedValueOffset INT

BEGIN TRANSACTION

--Obtain the current seed value - hold an exclusive lock

--on the table so that other connections cannot read the table's data.

--Be careful if using the SNAPSHOT and READ UNCOMMITTED isolation levels.

SELECT @.SeedValue = SeedValue

FROM dbo.TestTableSeed WITH (TABLOCKX, HOLDLOCK)

--Insert of sample data - substitute your actual INSERT statement into here

--and add in the ROW_NUMBER function.

--Important: ensure that the columns specified in both 'ORDER BY' clauses are the same.

INSERT INTO dbo.TestTable(TestTableID, TestValue)

SELECT TOP 10

@.SeedValue + ROW_NUMBER() OVER (ORDER BY [name] ASC) AS TestTableID,

[Name]

FROM sys.objects

ORDER BY [name]

--Obtain the number of rows affected by the INSERT statement.

--Be careful if you have triggers on the table into which the INSERT was performed.

SET @.SeedValueOffset = @.@.ROWCOUNT

--Update the seed table.

UPDATE dbo.TestTableSeed

SET SeedValue = @.SeedValue + @.SeedValueOffset

COMMIT TRANSACTION

--Return our new rows.

SELECT TestTableID,

TestValue

FROM dbo.TestTable

GO

/*

--Clean up.

DROP TABLE dbo.TestTable

GO

DROP TABLE dbo.TestTableSeed

GO

*/