Database Reference
In-Depth Information
Figure 5-17.
Adding files to a filegroup on the General page
When creating the new file, be sure to change the Path property of that file to point to a particular hard drive,
as shown in Figure
5-17
. The whole point of using filegroups is to be able to dictate which hard drive to use to
store a particular table's data. If you go to the trouble of creating filegroups and files without distributing the files
on multiple hard drives, you have just added administrative overhead with no benefit.
In addition, you should name your file with an
.NDF
extension rather than an
.MDF
or
.LDF
extension. All files
beyond the original
.MDF
file are given the extension of
.NDF
. The explanation of the file names are as follows: the
.MDF
file is your main data file, the
.LDF
is your log file, and any
.NDF
files are every other additional data file. If
you add six additional files, they will all have
.NDF
as their extension.
■
Note
These extensions are not case sensitive; they are capitalized here for clarity.
Once you have the filegroups and files created, you can add a table in a particular filegroup by using the
syntax shown in Listing 5-4. Although you cannot determine which file a table goes into, you can determine
which file group a table belongs to. This has the advantage of allowing you to have multiple files in a single file
group. If each file in the same filegroup is on a different hard drive, your table will span multiple hard drives.
Listing 5-4.
Creating Tables in a Particular Filegroup
/* (Note the following code is expected to error out and is only a demo) */
CREATE TABLE [dbo].[FactWeather]
([Date] [datetime] NOT NULL,
[EventKey] [int] NOT NULL,
[MaxTempF] [int] NOT NULL,
[MinTempF] [int] NOT NULL,
CONSTRAINT [PK_FactWeathers] PRIMARY KEY CLUSTERED ( [Date] ASC,[EventKey] ASC )
) ON [FactTables] -- Name of the File Group not the file!
■
We cover filegroups in this chapter because the Filegroups page is part of the new Database creation
window, but you most likely will not need to use them unless you are working with very large data warehouses.
Be aware that filegroups can give increased performance when reading and writing to multiple hard drives but
do not provide any fault tolerance to hard drive failure. If one drive fails, you have to replace the failed drive and
restore your most recent backup or at least refill the Data Warehouse with all of the oLTP data. Although this does
Note
