it indicates the filegroup to use. SQL Server allows you to specify multiple data files for storage, each of which can exist in a different filegroup, which can be in it's own location , sometimes you'll put different data onto different file groups for reasons of performance (more drives means more drive heads), or resilience (one filegroup may be RAID5, one might be RAID 1), or a number of other reasons. Heres some more detail from SQL BOL:
Database files can be grouped together in filegroups for allocation and administration purposes. Some systems can improve their performance by controlling the placement of data and indexes onto specific disk drives. Filegroups can aid this process. The system administrator can create filegroups for each disk drive, then assign specific tables, indexes, or the text, ntext, or image data from a table, to specific filegroups.
No file can be a member of more than one filegroup. Tables, indexes, and text, ntext, and image data can be associated with a filegroup, in which case all their pages will be allocated in that filegroup.
Log files are never a part of a filegroup. Log space is managed separately from data space.
Files in a filegroup will not autogrow unless there is no space available on any of the files in the filegroup.
There are two types of filegroups:
- Primary
The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.
- User-defined
User-defined filegroups are any filegroups specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.
One filegroup in each database operates as the default filegroup. When SQL Server allocates a page to a table or index for which no filegroup was specified when they were created, the pages are allocated from the default filegroup. Only one filegroup at a time can be the default filegroup. Members of the db_owner fixed database role can switch the default filegroup from one filegroup to another. If no default filegroup is specified, the primary filegroup is the default filegroup.
SQL Server 2000 can work quite effectively without filegroups, so many systems will not need to specify user-defined filegroups. In this case, all files are included in the primary filegroup and SQL Server 2000 can allocate data anywhere in the database. Filegroups are not the only method that can be used to distribute I/O across multiple drives.