October 29, 2009 at 7:00 am
Which is faster when moving a table to new filegroup: (table wheighs 400GB)
-----------------------------------------------------
--[1]
sp_rename 'TAB1' , 'TAB1_OLD' -- located on OLD_FG
GO
CREATE TABLE TAB1
( [primaryKey] [int] IDENTITY(1,1) NOT NULL,
[description] [varchar](2000) NULL,
[id] [varchar](15) NULL,
[name] [varchar](500) NULL,
[length] [int] NULL,
[bulkData] [image] NULL,
[timestamp] [varchar](20) NULL)
ON [new_FG] TEXTIMAGE_ON [new_FG]
GO
INSERT INTO TAB1 ()
SELECT
FROM TAB1_OLD
GO
DROP TABLE TAB1_OLD
GO
CREATE CLUSTERED INDEX [CIX_TAB1_id] ON TAB1([id]) ON [new_FG]
GO
ALTER TABLE TAB1 ADD CONSTRAINT [PK_TAB1] PRIMARY KEY NONCLUSTERED ([primaryKey]) ON [new_FG]
GO
-----------------------------------------------------------------
or
-----------------------------------------------------------------
--[2]
DROP INDEX [CIX_TAB1_id]
GO
ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO [new_FG])
GO
CREATE CLUSTERED INDEX [CIX_TAB1_id] ON TAB1([id]) ON [new_FG]
GO
ALTER TABLE TAB1 ADD CONSTRAINT [PK_TAB1] PRIMARY KEY NONCLUSTERED ([primaryKey]) ON [new_FG]
GO
October 30, 2009 at 6:47 pm
second should be faster since the move AND the creation of the clustered index are done in one step. BTW, be prepared for a pretty sizeable tlog for this evolution! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 2, 2009 at 6:18 am
Hi SQLguru,
I like your answer 🙂
thanx...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply