Moving table to new file group

USE Testing
GO

CREATE TABLE TAB1
(
TAB1_ID INT IDENTITY(1,1),
TAB1_NAME VARCHAR(100),
CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
)
GO



INSERT INTO TAB1(TAB1_NAME)
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

sp_help TAB1

ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO Second_table)

ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID) on third_index





SELECT O.Name AS [Object Name], O.[Type], I.name AS [Index name], I.Index_Id, I.type_desc AS [Index Type], F.name AS [Filegroup Name]
FROM sys.indexes I
INNER JOIN sys.filegroups F
ON I.data_space_id = F.data_space_id
INNER JOIN sys.objects O
ON I.[object_id] = O.[object_id]
GO







------------------------


USE Testing
GO

CREATE TABLE TAB2
(
TAB2_ID INT IDENTITY(1,1),
TAB2_NAME VARCHAR(100),
)
GO

CREATE INDEX IDX_TAB2 ON dbo.TAB2(TAB2_ID)
GO

INSERT INTO TAB2(TAB2_NAME)
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

sp_help TAB2


---moving table who is has on cluster index to another filegroup

CREATE CLUSTERED INDEX [TAB2_Cluster_Index]
ON [TAB2]([TAB2_ID])
ON [Second_table]

DROP INDEX TAB2.[TAB2_Cluster_Index]


---MOVING NON-CLUSTERED INDEX TO ANOTHER FILEGROUP

CREATE INDEX [IDX_TAB2]
ON [TAB2]([TAB2_ID])
WITH (DROP_EXISTING=ON, ONLINE=ON)
on THIRD_INDEX

---MOVING NON-CLUSTERED INDEX TO ANOTHER FILEGROUP FOR SQL SERVER 2005 STANDARD EDITION
CREATE INDEX [IDX_TAB2]
ON [TAB2]([TAB2_ID])
WITH (DROP_EXISTING=ON)
on THIRD_INDEX




SELECT O.Name AS [Object Name], O.[Type], I.name AS [Index name], I.Index_Id, I.type_desc AS [Index Type], F.name AS [Filegroup Name]
FROM sys.indexes I
INNER JOIN sys.filegroups F
ON I.data_space_id = F.data_space_id
INNER JOIN sys.objects O
ON I.[object_id] = O.[object_id]
GO