• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

How to have table reside in a filegroup that is different from the filegroup of indices

I'm using SQL Server 2014.  Can someone tell me how to make the table reside in one filegroup while its primary key and subsequent keys reside in another filegroup?

I wrote the following code specifying the primary key is to reside in a filegroup named @ndxFileGrp and the table in @dbsFileGrp.  But when I execute the code, the table and index are stored in the same filegroup named @ndxFileGrp.  Thank you for help.

ALTER PROCEDURE [dbo].[CreateTables] 
	-- Add the parameters for the stored procedure here
	@dataSrc char(5), 
	@yr int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SET ANSI_PADDING ON;

	DECLARE @tblName    VARCHAR(MAX), 
	        @tblPKey    VARCHAR(MAX),
		@dbsFileGrp VARCHAR(MAX),
		@ndxFileGrp VARCHAR(MAX),
		@sql        NVARCHAR(MAX);

    

	-- =============================
	-- CHARGE Table
	-- =============================
		

	IF UPPER(@dataSrc) <> 'ST_PA'
		BEGIN
			SELECT @tblName = 'dbo.' + UPPER(@dataSrc) + '_CHRG_' + STR(@yr,2);
			SELECT @tblPkey = UPPER(@dataSrc) + '_CHRG_' + STR(@yr,2) + '_PK';
			SELECT @dbsFileGrp = 'SQL_' + SUBSTRING(UPPER(@dataSrc), 1, 2) + SUBSTRING(UPPER(@dataSrc), 4, 2) + '_DBS_FG';
			SELECT @ndxFileGrp = 'SQL_' + SUBSTRING(UPPER(@dataSrc), 1, 2) + SUBSTRING(UPPER(@dataSrc), 4, 2) + '_IDX_FG';
			
			SELECT @sql = 'CREATE TABLE ' + RTRIM(@tblName) + 
			                          '(  [CNVRSN_SRC_CD]	varchar(5)		NOT NULL, ' +
						  '   [LHS_REC_NUM]		numeric(9, 0)	NOT NULL, ' +
						  '   [REV_SQNC_NUM]	numeric(3, 0)	NOT NULL, ' +
						  '   [REV_CHRG_AMT]	numeric(9, 2)	NULL, ' +
						  '   [REV_COST_AMT]	numeric(9, 2)	NULL, ' +
						  '   [RCV_DT]			datetime2(0)	NULL, ' +
						  '   [UNIT_OF_SRVC]	numeric(6, 0)	NULL, ' +
						  ' CONSTRAINT ' + @tblPkey + ' PRIMARY KEY CLUSTERED ' +
						  ' ( [LHS_REC_NUM]    ASC, ' +
						  '	  [CNVRSN_SRC_CD]  ASC, ' +
						  '   [REV_SQNC_NUM]   ASC  ' +
						  ' ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [' + @ndxFileGrp  + '] ' +
						  ') ON ' + @dbsFileGrp;

			IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
                          WHERE TABLE_NAME = @tblName)
			BEGIN
			    EXEC sp_executesql @sql;			      
			END
		
		END 

	SET ANSI_PADDING OFF

END

Open in new window

0
Maria Torres
Asked:
Maria Torres
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
PRIMARY KEY CLUSTERED
All clustered keys can't stay in another different filegroup from the table, as being clustered means to be physically in the table.

For non-clustered keys and indexes you can create or move them to any different filegroup.
0
 
Maria TorresData AnalystAuthor Commented:
Thank you for clarifying.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now