[dba-SQLServer] Placing indexes in a specific file

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?