by Syska
9. June 2010 16:10
So, how does one move a table to another filegroup.
Say I have the following table:
CREATE TABLE [dbo].[Tests](
[TestID] [int] NOT NULL,
[TestInt] [int] NOT NULL,
[TestBigInt] [bigint] NOT NULL,
CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Since tables are always stored with the clustered index. Moving the clustered index, also moves the table. So if we want to move the table, we can recreate the CLUSTERED index on another storage group. Here we are moving from “PRIMARY” to “PRIMARY2”.
CREATE UNIQUE CLUSTERED INDEX PK_Tests
ON dbo.Tests(TestID)
WITH DROP_EXISTING
ON [PRIMARY2]
This can also be done when the table is online and is also moving the indexes. There are some performance diff if you are moving the table while its still online, so consider if its worth the extra overhead.
You can extend the relation index options with the create index command like this, to move it while its still online.
CREATE UNIQUE CLUSTERED INDEX PK_Tests
ON dbo.Tests(TestID)
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON [PRIMARY2]
Subscribe