Snippet Name: Add_Filegroup_and_Table

Tags:

Created Date: 2025-06-16 13:12:40
Last Modified Date: 2025-05-31 10:32:19

Dependencies:

DBA Notes:

Sample Output:

Title: Add Filegroup and Table
Author: user
i have  a SQL Server database that is  running out of space. i want to add a file and file group on the database CIGDataMart_Docs on a disk location  I:\MSSQL\Data\, and when i create a table with this schema, to make sure it is located on the new drive. can you help me script that out. CREATE TABLE [dbo].[Application_DecisionInfo_Xml] ( 
[AppId]       INT                                     NOT NULL,
[InfoStatus]  NVARCHAR(25)                            NOT NULL,
[XmlData]     XML                                         NULL,
CONSTRAINT   [PK_Application_Decision_Xml]  PRIMARY KEY CLUSTERED    ([AppId] asc, [InfoStatus] asc)  WITH (DATA_COMPRESSION = PAGE ))

Author: ChatGPT
Absolutely, Lowell. Here's a step-by-step script to:

1. **Create a new filegroup** on the database.
2. **Add a data file** on the `I:\MSSQL\Data\` path to the new filegroup.
3. **Create your table** on that filegroup so that its data is stored on the new drive.

---

### ??? Step-by-step SQL Server Script

```sql
USE [master];
GO

-- 1. Add a new filegroup to the database
ALTER DATABASE [CIGDataMart_Docs]
ADD FILEGROUP [FG_XmlData_I];
GO

-- 2. Add a new file to that filegroup on the I: drive
ALTER DATABASE [CIGDataMart_Docs]
ADD FILE (
    NAME = N'CIGDataMart_Docs_XmlData_I',
    FILENAME = N'I:\MSSQL\Data\CIGDataMart_Docs_XmlData_I.ndf',
    SIZE = 512MB,
    FILEGROWTH = 128MB
) TO FILEGROUP [FG_XmlData_I];
GO

-- 3. Create the table on the new filegroup
USE [CIGDataMart_Docs];
GO

CREATE TABLE [dbo].[Application_DecisionInfo_Xml] 
( 
    [AppId]       INT            NOT NULL,
    [InfoStatus]  NVARCHAR(25)   NOT NULL,
    [XmlData]     XML            NULL,
    CONSTRAINT [PK_Application_Decision_Xml] 
        PRIMARY KEY CLUSTERED ([AppId] ASC, [InfoStatus] ASC)
        WITH (DATA_COMPRESSION = PAGE)
) 
ON [FG_XmlData_I];  -- Target the filegroup explicitly
GO
```

---

### ???? Notes:

- `FILEGROWTH = 128MB` can be tuned based on your growth expectations.
- If you're planning to store only XML-heavy or rarely-used tables here, you could even consider secondary compression options or partitioning.
- The `[FG_XmlData_I]` filegroup name is arbitrary???feel free to rename it if you have a naming convention.

Let me know if you want the XML column to be stored separately using a **XML filegroup** (`FILESTREAM`-like behavior) or need to make this table **partitioned** for future growth planning.