Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 31, 2002

SQL Server 2000 Administration in 15 Minutes a Week: Database Creation Basics - Page 2

By Michael Aubert


Database Filegroups 

In order to manage data files they need to be placed into "filegroups." By placing files into filegroups, we can control how data is spread over multiple files and we can also direct specific tables to specific files or sets of files.

When SQL Server allocates extents to tables, it does so by the proportional amount of the free space in all files in a filegroup. For example, let's say we had a filegroup that was made up of one file that had 50 MB free and another file that had 100 MB free. For every extent that was allocated from the first file, two extents would be allocated from the second file. Therefore the two data files would fill up at approximately the same time. 

Filegroups also allow control over what files a table will be stored in. For example, say we had 4 hard disks with 1 secondary data file on each disk. In our database let's say we have two large tables that we need to perform frequent join operations on. In order to get the best performance we can setup two file groups each containing two of the four data files. Next we can place each table in one of the filegroups to give us maximum performance. Note that we will see how to place tables and indexes in specific filegroups when we start creating tables in an upcoming article. 



There are a few specific filegroups we need to know about before we start creating our database. First, the Primary data file is always created as part of the "Primary filegroup" and it can't be removed from this group. The Primary filegroup contains all the system objects (system objects are objects that store information about the database) in the database. The system objects stored in the Primary filegroup can't be removed from this group.

When you create a new object it is automatically placed into the "Default" filegroup unless you specify otherwise. The Default filegroup can be set to any group you wish, but by default, the Default filegroup is set to the Primary filegroup (got that?). For example, if you created a new group called "MainStore" and set it as the Default filegroup, any new tables you create would automatically be placed into the "MainStore" filegroup.  

We will come back to the concept of filegroups when we start creating objects. Also, I will show you how to perform tasks such as moving objects between file groups then. So, if you don't have a total grasp on how filegroups fit in...don't worry. In a later article we will also take a more in-depth look at using files and filegroups to optimize database performance.


Page 3: Creating a Database


 » See All Articles by Columnist Michael Aubert



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM