sql server 2008: change default file group

I have 2 files with 2 bd group. I no longer wish to use the primary group file, I use this file PFG01 group. Enter the database properties and the site of filegroup, filegroup PFG01 dial by default, but does not work, that is, when I returned to add more records dbFileGroup increased the file that is in the primary group.
 I make that the new information is recorded on the file that is in the group PFG01

CREATE DATABASE dbFileGroup ON  PRIMARY
( NAME = 'dbPart', FILENAME = N'c:\SQLDATA\dbFileGroup.mdf' , SIZE = 3072KB , FILEGROWTH = 100KB )
 LOG ON
( NAME = 'dbPart_log', FILENAME = N'c:\SQLLOG\dbFileGroup_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

go

--
use dbFileGroup
go

CREATE TABLE [dbo].tabla_demo
(  
id int identity primary key,    
c varchar(200) NULL
)

--2
declare @i int
set @i = 1
while @i<=2 -- Luego 1.000.000 filas
begin        
      INSERT INTO dbo.tabla_demo    
      SELECT top (50000)      
               replace(cast(NEWID() as varchar(100)), '-','') + REPLICATE('a', 200-32)    
      FROM AdventureWorks.Sales.SalesOrderHeader  
            CROSS JOIN AdventureWorks.Sales.SalesOrderDetail
      print cast (@i as varchar(100))  
      set @i=@i+1
end

--Creación de los FileGroup
USE master
GO
ALTER DATABASE dbFileGroup ADD FILEGROUP PFG01

--Creación de los DataFile
ALTER DATABASE dbFileGroup ADD FILE
( NAME = N'dbFileGroup1',
  FILENAME = N'C:\SQLDATA\dbFileGroup1.ndf' , SIZE = 3072KB , FILEGROWTH = 512KB )
TO FILEGROUP PFG01

--
use dbFileGroup
go
select MAX(id) from dbo.tabla_demo

declare @i int
set @i = 1
while @i<=1
begin        
      INSERT INTO dbo.tabla_demo    
      SELECT top (50000)      
               replace(cast(NEWID() as varchar(100)), '-','') + REPLICATE('a', 200-32)    
      FROM AdventureWorks.Sales.SalesOrderHeader  
            CROSS JOIN AdventureWorks.Sales.SalesOrderDetail
      print cast (@i as varchar(100))  
      set @i=@i+1
end
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
setting a default filegroup will ONLY choose which filegroup a NEW table will go to.
new records will still go to the same filegroup(s) the table has been created in.

to move a table from 1 filegroup to another, you have to move it's clustered index to the new filegroup (aka create a [new] clustered index on the new filegroup)
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.