Move Large Table to New Filegroup

I started a new contract recently, and I found one of the databases to be fairly large in size, sitting on one drive... with about 20MB remaining, and very quickly running out of space.  Fortunately, there were other drives on the server with adequate space, so I decided to move one of the largest tables onto a different drive.  Here is a quick reference for you to see how to move an existing table to a new filegroup:

  /* ADD FILEGROUP */
  ALTER DATABASE DatabaseName
  ADD FILEGROUP NewFileGroupName
  GO

  /* ADD FILE  */
  ALTER DATABASE DatabaseName
  ADD FILE 
  ( NAME = NewFileName,
    FILENAME = 'I:\MSSQL\DATA\NewFileName.ndf',
    SIZE = 50, 
    FILEGROWTH = 1MB)
    TO FILEGROUP NewFileGroupName
   GO

Now that we've got our new filegroup and file, we can move an existing table to it by rebuilding the table's Primary Key.  First we need to run the drop constraint statement using the MOVE TO option, in order to move the table to the new filegroup.  Then, we can re-create the constraint.  See here:

  ALTER TABLE TableName
  DROP CONSTRAINT ConstraintName WITH (MOVE TO NewFileGroupName)
  GO

  ALTER TABLE TableName 
  ADD CONSTRAINT ConstraintName PRIMARY KEY(KeyName)
  GO

That's basically it.  You can run this to see that the table is now located on the new filegroup:

  EXEC sp_help TableName

Here is another nice query to let you see which objects reside upon each of your filegroups:  

Check your filegroups.