data between filegroup
-
hi all,
I have imported data from another database 'northwind' to my database 'TESTER'.
Before importing i have selected filegroup '2' as default which contains ndf file.
After successful transfer of data i change filegroup.
I have few question
1.why transfer data is visible in changed filegroup '3' after setting it as default
2.i have used
CREATE CLUSTERED INDEX [cluster_index] ON [dbo].[Categories] ([CategoryID])
with drop_existing ON [3].
After this query i drop my index.Then how will i test that my data in filegroup '2' has been transfered to filegroup '3'
Thanks,
Nero
-
1. The default option for the filegroup only sets the option that any data inserted from that point on will be written in that filegroup. SQL Server "sees" the data in all filegroups as one. So setting a filegroup as defeault does not mean the data from the other filegroups will not bevisisble.
You can implement archiving in your SQL Server to split data between tables or databases so that the data will not be visible
Here is an article on archiving:
http://www.extremeexperts.com/SQL/yukon/ArchivingData.aspx
2. Use this query to determine all the tables in a certain filegroup:
USE databasename
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
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 = 2 --* New FileGroup*
GO
-
sorry, but could you please clarify this statement and the value/variable that it should be filled with?
INNER JOIN sys.filegroups f
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply