[dba-SQLServer] Placing indexes in a specific file

Hi John:

To move a table from one filegroup to another, from EM, go into design view 
of the table, bring up properties.  on the first tab ("tables"), the third 
last dropdown list is table filegroup.  You can use this dropdown list to 
move table from one filegroup to another.  When you move tables from one 
filegroup to another, this is what EM does is recreate a temporarily copy of 
the table in the target filegroup, copy over data, drop old table and rename 
new one.

You can also move indexes from one filegroup to another via same way (ie. 
table properties, indexes/keys, index filegroup dropdown list).

Filegroups is just a way of organizing your data files.  Filegroups consist 
of physical data files.  AFAIK, I don't think it is possible to put data of 
a table in a specific physical data file (you can only put table into file 
group. sql server does the rest).  For performance reasons, in a single 
filegroup, it is recommended that your physical data files are spread out 
over multiple disks.  That way all disks can work simultaneously at once to 
get your data for a single table.

Not sure what is the question "Do I need to create a new file on a new 
drive, make it a new file group?, Then a new file on a second disk, making 
it a different filegroup?  Repeat until all files are in a different 
filegroup?  This all seems terribly cumbersome."  I believe that you can add 
more new data files (on a new disk) to an existing filegroup if you run out 
of space on the disks on the existing data files.

I don't remember how SQL Server allocates the data to the physical data 
files.  (ie. if filegroup has multiple data files, when you insert a record 
into table within that filegroup, which data file is the record physically 
stored in?).  I don't have my sql server book with me so maybe somebody else 
knows.

HTH
Billy

>From: "John W. Colby" <jwcolby at colbyconsulting.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Placing indexes in a specific file
>Date: Sun, 23 Jan 2005 15:49:17 -0500
>
>Billy,
>
>Thanks for the response.  Unfortunately I have already created most of the
>tables, and in fact already moved the data into most of the tables.  I
>created the tables by simply opening the big table in design view, 
>selecting
>the fields I wanted in the smaller table, copying the fields to the paste
>buffer, creating a new table and pasting the fields in to the new table and
>saved.  I then opened QA, did a "Script object to new window as SELECT"
>, then "Script object to new window as INSERT", cut the select statement 
>and
>pasted it into the VALUES clause of the insert, and finally ran the insert
>statement.  Quick and easy.  The data gets moved from the BigTable to the
>new table in a couple of hours (depending on how many I run 
>simultaneously).
>I then go in to each small table and build the indexes manually.  Close and
>save the table and the tables are built.
>
>As you can see I never used a SQL statement for any of this.  It is very
>fast to do it manually, and that process of building the smaller tables is
>almost complete.  I do still have to index about half of the smaller 
>tables,
>but because of the data in them I don't create clustered indexes.
>
>Thus I need to know if I can take an existing table and move it to a
>specific file.  I suppose I could a INSERT INTO NEWSmallTable SELECT * FROM
>SmallTable ON NEWFileGroup?  That would copy the existing small tables I
>have created to the new file?
>
>I must admit that I am confused about the concept of FileGroup.  AFAICT all
>of the files I currently have are in one file group.  Thus using an On
>FILEGROUP would only get it into the set of 4 files, not into a specific
>file of that file group.
>
>Do I need to create a new file on a new drive, make it a new file group?,
>Then a new file on a second disk, making it a different filegroup?  Repeat
>until all files are in a different filegroup?  This all seems terribly
>cumbersome.
>
>John W. Colby
>www.ColbyConsulting.com
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy Pang
>Sent: Sunday, January 23, 2005 2:57 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Placing indexes in a specific file
>
>
>Hi John:
>
>To improve disk I/O performance, place data of table and its indexes in
>separate data files (because less contention for disk resources given the
>way data is accessed on disk).
>
>The syntax for creating index on specific filegroup is:
>
>"BOL Syntax"
>CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
>     ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
>[ WITH < index_option > [ ,...n] ]
>[ ON filegroup ]
>
>For example:
>create index ix_myindex on products(product_name) on secondary_file_group;
>
>To store the table in a specific data file, use the ON filegroup at the end
>of CREATE TABLE statement or create a clustered index of that table in the
>target filegroup.  The data of a table resides in the same filegroup as its
>clustered index.
>
>Note: each db has default filegroup.  If you don't specify specific
>filegroup when you create index/table, then that is where the index/table
>will reside.
>
>Always create your clustered index first before creating your nonclustered
>indexes of a table.  This is because physical key in nonclusterd indexes is
>composed of the clustered index key.  If you create nonclustered index
>first, and then clustered index, the db will need to rebuild existing
>nonclusterd indexes of that table (argh!).
>
>Sometimes an existing index on a table helps speed up creation of other
>indexes on the table.
>
>HTH
>
>Billy
>
> >From: "John W. Colby" <jwcolby at colbyconsulting.com>
> >Reply-To: dba-sqlserver at databaseadvisors.com
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: RE: [dba-SQLServer] Placing indexes in a specific file
> >Date: Sun, 23 Jan 2005 13:47:45 -0500
> >
> >On the same note, is it possible to cause specific tables to be placed
> >in specific files?
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>