[SQL Server 2005] Primary vs Secondary file-groups - deciding which table goes where

I created a set of primary and secondary data-files and organized them into file-groups. Now, I want to decide which tables to allocate to which file-group. What should be the factors on which I ought to decide this? Is there a significance of choosing the primary group over the secondary ones or vice versa? What is the impact of the choice in either case?filegroups in general are all about PERFORMANCE. you can gain significant performance gains if you leverage multiple disks with the multi-filegroup feature of sql server. there is no hard and fast but the general idea is to spread the workload evenly over the disks and to leverage faster disks/cache for the more intensive files|||

Thank you for your reply, Derek. Need one clarification though - would it be correct to say that a table created on a file-group gets evenly distributed across all the data-files in the file-group? In effect, what we are achieving is partitioning the table, right?

My earlier question was more about understanding how SQL Server determines which table belongs to which data-file and file-group. I suppose it might be part of metadata, which some DMV would be able to retrieve. I am not sure but, if this is indeed so, which is the DMV?

A related question that pops up is - should all (or most of the) tables that are most frequently accessed by allocated to the primary file-group? If SQL Server looks at the primary file-group first before scanning the others for the desired table-rows, it would make sense in allocating the most frequently accessed tables to the primary file-group.

[newbie] SQL-query with SUM() doesnt work. :(

Hi NG,

I have two tables:

TABLE1 (id1 INTEGER NOT NULL, id2 INTEGER NOT NULL, value INTEGER, PRIMARY KEY (id1, id2))

TABLE2 (id1 INTEGER NOT NULL, text CHAR(4), PRIMARY KEY (id1))

And this is the query, that I'm trying to run on this tables:

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

Well - it doesn't work at all! :(
That is the reason?!

Appreciate everyone's help!!!

S.B.well when doing aggregates you must have everything in the select statement that is not being summed in the group by clause.

Example:

select t1.id1, t2.text, sum(t1.value)
from table1 t1, table2 t2
where t1.id1 = t2.id1
group by t1.id1, t2.text
order by t1.id1 asc|||Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||Thanks, mkkmg and alligatorsql.com !!! :)

"group by TABLE1.id1, TABLE2.text" works perfectly!

S.B.

Originally posted by alligatorsql.com
Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com