How to add multiple file groups to one file

[mflancour]

If possible please provide the code.

 

[mflancour]

hmm, upon further investigation I think I was a bit mixed up when I asked my question. What I am trying to do is create a table that uses a varchar partition for letters a through z. this is to help speed up queries against this table. I was originally thinking I'd just put everything into one file but I don't know if that is actually even possible, I just thought it would be a real pain to maintain 27 different files for just 1 table.

 

[mrdenny]

What version of SQL Server?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mflancour]

2005, I figured out what I was doing wrong. I decided to break it up into 5 files. Here's the code for anyone that care's

--------------------
use ThisDB
go

 CREATE PARTITION FUNCTION pf_email (VARCHAR(100)) 
	 AS RANGE LEFT FOR VALUES (
	'A', 
	'B', 
	'C', 
	'D', 
	'E', 
	'F', 
	'G', 
	'H', 
	'I', 
	'J', 
	'K', 
	'L', 
	'M', 
	'N', 
	'O',
	'P', 
	'Q', 
	'R', 
	'S', 
	'T', 
	'U', 
	'V', 
	'W',
	'X', 
	'Y', 
	'Z'
	)
go
------------------
ALTER DATABASE ThisDB ADD FILEGROUP AlphaFGA_E
ALTER DATABASE ThisDB ADD FILEGROUP AlphaFGF_J
ALTER DATABASE ThisDB ADD FILEGROUP AlphaFGK_O
ALTER DATABASE ThisDB ADD FILEGROUP AlphaFGP_T
ALTER DATABASE ThisDB ADD FILEGROUP AlphaFGU_Z
go
-----------------

alter database ThisDB add file (name = 'AlphaFGA_E', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ThisDBFAlphaA_E.ndf') to filegroup [AlphaFGA_E];
alter database ThisDB add file (name = 'AlphaFGF_J', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ThisDBFAlphaF_J.ndf') to filegroup [AlphaFGF_J];
alter database ThisDB add file (name = 'AlphaFGK_O', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ThisDBFAlphaK_O.ndf') to filegroup [AlphaFGK_O];
alter database ThisDB add file (name = 'AlphaFGP_T', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ThisDBFAlphaP_T.ndf') to filegroup [AlphaFGP_T];
alter database ThisDB add file (name = 'AlphaFGU_Z', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ThisDBFAlphaU_Z.ndf') to filegroup [AlphaFGU_Z];
go
--------------------


create partition scheme ps_email
as partition pf_email to (
AlphaFGA_E, AlphaFGA_E, AlphaFGA_E, AlphaFGA_E, AlphaFGA_E, AlphaFGA_E,
AlphaFGF_J, AlphaFGF_J, AlphaFGF_J, AlphaFGF_J, AlphaFGF_J, 
AlphaFGK_O, AlphaFGK_O, AlphaFGK_O, AlphaFGK_O, AlphaFGK_O,
AlphaFGP_T, AlphaFGP_T, AlphaFGP_T, AlphaFGP_T, AlphaFGP_T, 
AlphaFGU_Z, AlphaFGU_Z, AlphaFGU_Z, AlphaFGU_Z, AlphaFGU_Z, AlphaFGU_Z
)
go
--------------------------

CREATE TABLE [dbo].[ThisTable](
	[email] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[feed_id] [int] NOT NULL,
	[list_id] [int] NOT NULL,
	[domainName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[firstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[lastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[address1] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[address2] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[city] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[state] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[zip] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[country] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[homePhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[workPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[dob] [datetime] NULL,
	[gender] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ip] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[source] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[optinDate] [datetime] NULL,
	[date_inserted] [datetime] NOT NULL CONSTRAINT [DF_FeedMaster_date_inserted]  DEFAULT (getdate()),
	[date_Modified] [datetime] NULL
) ON [ps_email]([email])


go