September 20, 2005 at 10:47 am
Hello Again, Folks
Today i've got a question that has been issued to me and i'm unable to answer it: ¿Is it possible to have tables on the same Database in different data files? and ¿How could it be done?...
Thanks and sorry about my grammar.
September 20, 2005 at 11:10 am
Create a new file group and move the clustered index on that filegroup.
September 20, 2005 at 11:13 am
Yes, but you should probably read up on files and filegroups b4 you proceed.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6epf.asp
a db can have many files in 1 filegroup . Which sql server will spread data across the files.
Or it can have many filegroups, and each filegroup can have many files. within each filegroup the data will be spread across the files. I have generally created 1 file per filegroup.
When you create a table. The syntax in the on clause tells it what filegroup to be placed on. Generally you'll see ON PRIMARY.
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }
| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
Also as Remi States, the filegroup that the clustered index is placed on is where the data will live.
September 21, 2005 at 2:29 pm
One might also ask the reason for putting a table on a separate filegroup. Performance based reasons may be acceptable for local disk configurations. So if you are using SAN disk, then this will buy you nothing. If you're using local disk, then by all means read the link Ray_M provided. If you reasons for exploring separate filegroups are for backup and recovery by filegroup then be careful about index placement. There are even reasons to perform this on very large tables for data archival - rolling off tables/filegroups as time passes. This is usually associated with horizontal table partitioning and select union all views for large databases.
No matter what your reason, if you use multiple filegroups I do advise only letting the system tables reside on the promary filegroup <period>. Then you can slice'n'dive as many as SQL 2K wilkl let you have. Oh, don't forget to set on of your new filegroups to be the 'default' !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 27, 2005 at 3:53 am
i'm workin on local disk, is a little SQL 2000 Server with a 4Gb Database, but the data on the table is not often used and they want to keep it in a separate file...so the link provided above is perfect for my need.
Thnx Again Friends!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply