Tony Rogerson's ramblings on SQL Server

There are many occaisons where you may want to use multiple filegroups, especially if dealing with large tables and fragmentation is a worry.

The script below shows a complete end-to-end example of how to create a file group and mark it as the default so all newly created objects are placed on there.

Note, there is nothing you can do about [PRIMARY] so if you have creation scripts or drop / recreate index scripts with [PRIMARY] in them then that data will always go on the [PRIMARY] file group.

-- Create the database
CREATE DATABASE test_filegroup
go

USE test_filegroup
go

-- Create a new file group
ALTER DATABASE test_filegroup 
   ADD FILEGROUP NonClustIndexes
go

ALTER DATABASE test_filegroup
   ADD FILE (
      NAME = NonClustIndexes,
      FILENAME = 'E:\MSSQL\DEV\NonClustIndexes.ndf',
      SIZE = 5MB,
      MAXSIZE = 100MB,
      FILEGROWTH = 5MB
      )
   TO FILEGROUP NonClustIndexes

-- Change the default filegroup for where new tables and indexes are created.
ALTER DATABASE test_filegroup
   MODIFY FILEGROUP NonClustIndexes DEFAULT;

-- Test this
CREATE TABLE xyz (
   mydata int not null constraint pk_xyz primary key clustered,

   ) ON [PRIMARY]

INSERT xyz values( 1234 )
GO

-- This shows that the table was created on the [PRIMARY] file group and not our new user defined group
sp_help xyz
go

-- This does not move the table
DBCC DBREINDEX( xyz )
go

-- Nor does this
CREATE UNIQUE CLUSTERED INDEX pk_xyz ON xyz( mydata ) WITH DROP_EXISTING
GO

-- This does though
CREATE UNIQUE CLUSTERED INDEX pk_xyz ON xyz( mydata ) WITH DROP_EXISTING ON NonClustIndexes
GO

-- Table is now located on the NonClustIndexes filegroup
sp_help xyz
GO

-- Still on the NonClustIndexes filegroup
DBCC DBREINDEX( xyz )
GO

-- And still there.
CREATE UNIQUE CLUSTERED INDEX pk_xyz ON xyz( mydata ) WITH DROP_EXISTING
GO

-- What about new objects?
CREATE TABLE z (
   mydata int not null constraint pk_z primary key clustered,
   )
GO

-- Correctly placed on our NonClustIndexes filegroup
sp_help z