Listing 1: Sample Code to Create a Database with Multiple Filegroups 

USE master
GO

-- Creating a database with multiple filegroups
IF DB_ID('MyDB') IS NOT NULL
   DROP DATABASE MyDB
GO

CREATE DATABASE [MyDB]
ON PRIMARY (
   NAME = 'MyDB',
   FILENAME = N'C:\MyDB_Primary.mdf'),
FILEGROUP MyFileGroup (
   NAME = 'MyDBFileGroup2',
   FILENAME = N'D:\MyDB_MyFilegroup.ndf')
GO

-- Verifying file info
USE MyDB
GO
-- Option I:
EXEC sp_helpfile
-- Option II:
SELECT * FROM sysfiles
GO

Listing 2: Sample Code Containing the Data Definition Language (DDL) for the Table 


IF OBJECT_ID('[dbo].[utbMyTable1]') IS NOT NULL
   DROP TABLE [dbo].[utbMyTable1]
GO

CREATE TABLE [dbo].[utbMyTable1]
   ([Col1] INT IDENTITY(1, 1),
    [Col2] NVARCHAR(10),
    [Col3] BIGINT,
    [Col4] IMAGE)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[utbMyTable1]
ADD CONSTRAINT [PK_utbMyTable1]
PRIMARY KEY CLUSTERED ([Col1])
GO

CREATE INDEX IX_utbMyTableCol2
ON [dbo].[utbMyTable1]([Col2])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[utbMyTable1]
ADD CONSTRAINT [UQ_utbMyTable1_Col3]
UNIQUE([Col3])
ON [PRIMARY]
GO

Listing 3: Sample Code to Return Filegroup Information for Indexes and Constraints 


SELECT a.indid, a.groupid, a.name, b.groupname
FROM sysindexes a
   INNER JOIN sysfilegroups b
   ON a.groupid = b.groupid
WHERE a.id = OBJECT_ID('[dbo].[utbMyTable1]')
   AND INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0


CREATE UNIQUE CLUSTERED INDEX [PK_utbMyTable1]
ON [dbo].[utbMyTable1] ([Col1])
WITH DROP_EXISTING
ON [MyFilegroup]
GO