December 21, 2009 at 11:46 am
[font="Courier New"]Here is my scenario.
Actually, i have kept TSTDB_Data as the default file group. But the developer created the scripts
for all the tables with PRIMARY as the filegroup. So now i have to change it for 200+ tables.
USE [TSTDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [TSTCAT].[ZVENDOR](
[T1_KEY] [bigint] NOT NULL,
[STD_KEY] [bigint] NOT NULL,
[REF_KEY] [bigint] NOT NULL,
[POINTER_KEY] [bigint] NOT NULL,
[KOUNT] [bigint] NOT NULL,
CONSTRAINT [ZZ_PK_VENDOR] PRIMARY KEY CLUSTERED
(
[T1_KEY] ASC,
[STD_KEY] ASC,
[REF_KEY] ASC,
[POINTER_KEY] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
I have two filegroups TSTDB_Idx , TSTDB_Data.
The above script where the filegroup is not mentioned creates the table on the Primary FileGroup.
Now i need to move the pkey constraint/index alone to TSTDB_Idx keeping the data in TSTDB_Data.
So this is what i did for the index
USE [TSTDB]
GO
ALTER TABLE [TSTCAT].[ZVENDOR] DROP CONSTRAINT ZZ_PK_VENDOR;
ALTER TABLE [TSTCAT].[ZVENDOR]
ADD CONSTRAINT ZZ_PK_VENDOR PRIMARY KEY
(
[T1_KEY] ASC,
[STD_KEY] ASC,
[REF_KEY] ASC,
[POINTER_KEY] ASC
)
ON TSTDB_Idx
GO
But when i run the query
/*
-- Verify filegroup location of the clustered index.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
i.data_space_id, f.name AS [Filegroup Name],t.object_id
FROM sys.indexes AS i
JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
JOIN sys.tables as t ON i.object_id = t.object_id
AND i.object_id = OBJECT_ID(N'TSTCAT.ZVENDOR','U')
*/
/* Get Details of Object on different filegroup
Finding User Created Tables*/
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
,i.object_id
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
and i.object_id = 1226697561
ORDER BY 1 DESC
GO
The following output is produced.
name type name index_id name object_id
-------- ---- -------------------- --------- ----------------- -----------
ZVENDOR U ZZ_PK_VENDOR 1 TSTDB_Idx 1226697561
(1 row(s) affected)
Does this mean that ONLY the index is moved to TSTDB_Idx but the data part is still
remaining in the PRIMARY file group?.
How do i move the data portion of the table point to TSTDB_Data away from Primary.[/font]
December 21, 2009 at 11:56 am
gk-411903 (12/21/2009)
Does this mean that ONLY the index is moved to TSTDB_Idx but the data part is stillremaining in the PRIMARY file group?.
No. That's index 1, the clustered index. The clustered index IS the table.
Where you'll have a problem is with tables that don't have clustered indexes and LOB data
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
December 21, 2009 at 12:03 pm
GilaMonster (12/21/2009)
gk-411903 (12/21/2009)
Does this mean that ONLY the index is moved to TSTDB_Idx but the data part is stillremaining in the PRIMARY file group?.
No. That's index 1, the clustered index. The clustered index IS the table.
Where you'll have a problem is with tables that don't have clustered indexes and LOB data
As Gail has stated, LOB data and Heap tables will require a little more work to move them.
For Heap tables, it would be as simple as creating a clustered index on the table pointing it to the new filegroup.
As for the LOB, you would need to create a new "twin" table in the correct filegroup, copy the data into it, drop the old table and rename the new "twin" table to the same name as the one just dropped. You will also need to ensure that FKs are still intact.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 1:58 pm
Forgive my ignorance. I understand that the index is moved to the desired filegroup (index file group). But is the data also in the same index file group? I don't understand what you meant by "Clustered Index IS data".
In the meantime i tried ALTER TABLE DROP CONSTRAINT WITH (MOVE TO index_file_group) followed by ALTER TABLE ADD CONSTRAINT PRIMARY KEY CLUSTERED...
But
declare @i int
set @i = object_id('schema.Table')
exec sp_objectfilegroup @i
brings the Index tablespace. So , does this mean i CANNOT have my data separately? Pardon my ignorance.
TIA
December 21, 2009 at 2:38 pm
gk-411903 (12/21/2009)
Forgive my ignorance. I understand that the index is moved to the desired filegroup (index file group). But is the data also in the same index file group? I don't understand what you meant by "Clustered Index IS data".In the meantime i tried ALTER TABLE DROP CONSTRAINT WITH (MOVE TO index_file_group) followed by ALTER TABLE ADD CONSTRAINT PRIMARY KEY CLUSTERED...
But
declare @i int
set @i = object_id('schema.Table')
exec sp_objectfilegroup @i
brings the Index tablespace. So , does this mean i CANNOT have my data separately? Pardon my ignorance.
TIA
A clustered Index is the actual data, sorted in the order specifed in the index. It generally resides in the PRIMARY filegroup, but can be moved, meaning that all the data gets moved.
A non-clustered index is a pointer to the data, so it can reside in a different filegroup from the data.
BOL or the more knowledgable members here can give you a better explanation.
December 21, 2009 at 4:24 pm
I found this URL as a good reference point.
Thanks for all those who answered.
December 21, 2009 at 4:43 pm
You're welcome. Thanks for the feedback.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply