Creating Tables in a Filegroup

3 4

SQL Server lets you specify which filegroup you want each table and its data to be placed in—assuming that you have created one or more user-defined filegroups. If no filegroup is specified at table creation, tables will be placed in the primary filegroup unless another filegroup has been assigned to be the default filegroup. (Filegroups are used to place files and indexes on a particular disk or array of disks. See Chapter 9 for more information about how and why table data is placed in files and filegroups.)

Creating the Product_Info Table in a Filegroup

Suppose we create the MyDB database with a filegroup named product_group that contains one secondary data file that is on a different disk drive (E) from the disk drive housing the primary filegroup (C). This technique allows us to physically separate our data tables from the SQL Server system tables. We'll also create the log file on a different drive (F) to separate log I/O. (Creating databases and using filegroups is explained in Chapter 9.) The commands might look like this:

 USE master GO CREATE DATABASE MyDB ON PRIMARY --Explicitly states primary -- filegroup (optional) (NAME = MyDBroot, --Primary data file FILENAME = 'c:\mssql2k\MSSQL\data\mydbroot.mdf', SIZE = 8MB, MAXSIZE = 10MB, FILEGROWTH = 1MB), FILEGROUP product_group --Filegroup for next file (NAME = MyDBdata1, --Secondary data file FILENAME = 'e:\mssql2k\MSSQL\data\mydbdata1.ndf', SIZE = 1000MB, MAXSIZE = 1500MB, FILEGROWTH = 100MB) LOG ON (NAME = Logdata1, --Log file FILENAME = 'f:\log_files\logdata1.ldf', SIZE = 1000MB, MAXSIZE = 1500MB, FILEGROWTH = 100MB) GO 

We can now create the Product_Info table in the product_group filegroup using the CREATE TABLE command, as shown here:

 USE MyDB GO CREATE TABLE Product_Info ( Product_ID smallint, Product_Name char(20), Description char(30), Price smallmoney, Brand_ID brand_type ) on product_group GO 

The table and all data inserted in the table will be placed on drive E, the drive on which product_group was defined. Thus, the Product_Info table data will have a dedicated drive for its I/O, as long as no other tables are created in this same filegroup.