June 11, 2009 at 12:55 pm
How can I move a table from one filegroup to another filegroup in following scenarios:
1. Move a table without any index or primary key or constraints
2. Move a table with clustered index and primary key
3. Move a table with nonclustered index.
Thanks in advance.
June 11, 2009 at 1:08 pm
RPSql (6/11/2009)
1. Move a table without any index or primary key or constraints
Create a clustered index and specify ON with the new filegroup. Then drop the clustered index
2. Move a table with clustered index and primary key
Rebuild the clustered index on the other filegroup. Use CREATE DATABASE ... WITH DROP_EXISTING
3. Move a table with nonclustered index.
Depends. If it has a clustered index, same as 2. If it doesn't, same as 1.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 1:21 pm
Can you please give example with script for this?
Thanks.
June 11, 2009 at 1:36 pm
Here is what I use. I create or rebuild de cluster index or primary key within a transaction to protect the integrity of the table in case of failure:
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
CREATE CLUSTERED INDEX ix_TableA_Test ON tableA (id) ON [OtherFileGroup]
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
coments?
Alberto
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
June 11, 2009 at 1:38 pm
I think this way is better:
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
CREATE CLUSTERED INDEX ix_TableA_Transfer ON tableA (id) ON [PRIMARY]
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
June 11, 2009 at 1:39 pm
This is the one, promise. Sorry for the inconvenience
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
CREATE CLUSTERED INDEX ix_TableA_Transfer ON tableA (id) ON [OtherFileGroup]
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
June 11, 2009 at 2:01 pm
Alberto De Rossi (6/11/2009)
This is the one, promise. Sorry for the inconvenience
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
CREATE CLUSTERED INDEX ix_TableA_Transfer ON tableA (id) ON [OtherFileGroup]
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Great if a cluster doesn't already exist. If oen does, you'll need to add the WITH DROP_EXISTING clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 2:02 pm
Can you please give detailed description or any article for this for moving table with clustered index, non clustered index and a table without any index...I have tried using this script but it only works for tables without any index right?
June 11, 2009 at 2:08 pm
You can ONLY move a table with a clustered index. You do this by rebuilding the clustered index on the new filegroup. If you don't have one, just create one on the new filegroup. The scripts above will work.
June 11, 2009 at 2:13 pm
RPSql (6/11/2009)
Can you please give detailed description or any article for this for moving table with clustered index, non clustered index and a table without any index...
See my initial reply.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 2:22 pm
I have two questions...
1. For a table without any indexes, as you said I can move it to another filegroup by using above script(which creates clustered index on new filegroup). I have tried that and that worked. But when I checked table property it says that Filegroup="New File Group" and "Text filegroup"="Old File Group"..Why is it so? Why on Text filegroup it mentions Old file group. That table is 16 GB table but after I run above script, the new filegroup's file size is only one GB.
One more thing, after using above script,Can I drop that index? I won't lose any data by doing so, right?
2. How can I know the contents of filegroup i.e. what tables and indexes are stored in that filegroup.
June 11, 2009 at 2:25 pm
RPSql (6/11/2009)
But when I checked table property it says that Filegroup="New File Group" and "Text filegroup"="Old File Group"..Why is it so? Why on Text filegroup it mentions Old file group. That table is 16 GB table but after I run above script, the new filegroup's file size is only one GB.
The Text On refers to where the Large Object data types are (text, ntext, image, varchar(max), nvarchar(max), varbinar(max)). They cannot be moved to a new filegroup. The filegroup that the text image is on when the table is created is the one that it will always be on
One more thing, after using above script,Can I drop that index? I won't lose any data by doing so, right?
Yup.
2. How can I know the contents of filegroup i.e. what tables and indexes are stored in that filegroup.
Try the system view sys.data_spaces. If it's not that one it's one of the related ones. Check in Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 2:46 pm
Thanks a lot for all help. I checked book online but can't fine the answer, thus I put question here....
Sys.dataspaces Contains a row for each data space. This can be a filegroup or partition scheme.
While, Sys.filegroups also Contains a row for each data space that is a filegroup.
But I would like to know about how many tables and indexes are there in each file groups.
June 11, 2009 at 4:27 pm
Here you have a couple of scripts that show you where are the objects:
TABLES:
SELECT
t.name AS [Table],
s.name AS [Schema],
ds.name AS [FileGroup]
FROM
sys.tables AS t
INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
INNER JOIN sys.indexes AS i ON i.object_id = t.object_id and i.index_id < 2
INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
WHERE
s.name='MySchema'
GO
INDEXES:
SELECT t.name AS [Table], s.name AS [Schema], i.name AS [Index], i.type_desc AS [IndexType], f.name AS [FileGroup]
FROM sys.objects t
INNER JOIN sys.indexes i ON
t.object_id = i.OBJECT_ID
INNER JOIN sys.filegroups f ON
i.data_space_id = f.data_space_id
INNER JOIN sys.schemas AS s ON
t.schema_id = s.SCHEMA_ID
WHERE t.TYPE = 'U'
ORDER BY f.name, t.name, i.type_desc
GO
For the second, you need to have basoc knowledge of HEAP, CLUSTERED AND NONCLUSTERED INDEXES
any coments?
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
June 11, 2009 at 4:28 pm
ah! I forgot to say that the scripts work for SQL 2005/2008
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply