MS SQL 2008 +: TSQL:: Partitioning and Parallelism

Hi team,
I am learning how to create file groups, partition functions, partitions schemes, partition tables, and of course, examine around 100K records and play around with the performance and enhancements and hopefully learn something today. So to speak.

Downloaded the USPS ZipCode table (around 82K records)  and good enough for what I like to do for now.
The code in below creates all the needed goodies (I hope), everything looks good until I tried to INSERT INTO TheParitionedTable from the existing non-partitioned ZipCode table.



use admin
go


ALTER DATABASE [Admin] ADD FILEGROUP FG0;
ALTER DATABASE [Admin] ADD FILEGROUP FG1;
ALTER DATABASE [Admin] ADD FILEGROUP FG2;
ALTER DATABASE [Admin] ADD FILEGROUP FG3;
ALTER DATABASE [Admin] ADD FILEGROUP FG4;
ALTER DATABASE [Admin] ADD FILEGROUP FG5;
ALTER DATABASE [Admin] ADD FILEGROUP FG6;
ALTER DATABASE [Admin] ADD FILEGROUP FG7;
ALTER DATABASE [Admin] ADD FILEGROUP FG8;
ALTER DATABASE [Admin] ADD FILEGROUP FG9;
ALTER DATABASE [Admin] ADD FILEGROUP FG10;


-- drop partition function mypf 

create partition function MyPF(nvarchar(128))
as range right for values (00001, 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000);


--drop partition scheme myps 

create partition scheme MyPS
as partition MyPF
to (FG0, FG1, FG2, FG3, FG4, FG5, FG6, FG7, FG8, FG9, FG10);
go



--drop table tbl_partitioned_zipcodes 

CREATE TABLE [dbo].tbl_Partitioned_ZipCodes(
	[RecordNumber] [nvarchar](128) NULL,
	[Zipcode] [nvarchar](128) NULL,
	[ZipCodeType] [nvarchar](128) NULL,
	[City] [nvarchar](128) NULL,
	[State] [nvarchar](128) NULL,
	[LocationType] [nvarchar](128) NULL,
	[Lat] [nvarchar](128) NULL,
	[Long] [nvarchar](128) NULL,
	[Xaxis] [nvarchar](128) NULL,
	[Yaxis] [nvarchar](128) NULL,
	[Zaxis] [nvarchar](128) NULL,
	[WorldRegion] [nvarchar](128) NULL,
	[Country] [nvarchar](128) NULL,
	[LocationText] [nvarchar](128) NULL,
	[Location] [nvarchar](128) NULL,
	[Decommisioned] [nvarchar](128) NULL,
	[TaxReturnsFiled] [nvarchar](128) NULL,
	[EstimatedPopulation] [nvarchar](128) NULL,
	[TotalWages] [nvarchar](128) NULL,
	[Notes] [nvarchar](128) NULL
) on MyPS(ZipCode)

GO

insert into tbl_Partitioned_ZipCodes
	select * from USPS_ZipCodes 



go

Open in new window



The error message after the Insert attempt is as follows:
Msg 622, Level 16, State 3, Line 1
The filegroup "FG0" has no files assigned to it. Tables, indexes, 
    text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added.

Open in new window


Any ideas and assistance will be greatly appreciated
thx

JohnE
John EsraeloDatabase / SQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

micropc1Commented:
You need to add files to the filegroup. See here..

http://msdn.microsoft.com/en-us/library/bb522469.aspx

lke...

ALTER DATABASE [Admin]
ADD FILE
(
    NAME= 'fileName',
    FILENAME = 'filePath'
)
TO FILEGROUP FG0

Open in new window

0

Experts Exchange Solution brought to you by

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
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Excellent point.
I went ahead and added the following for each file group until all 10 of them were physically created on the C drive .
ALTER DATABASE [Admin] ADD FILEGROUP FG0;
alter database [admin] add file
	(
	    NAME = FG0_File,
		FILENAME = 'C:\DBA\DATA\FG0_File.ndf',
		SIZE = 5MB,
		MAXSIZE = 10MB,
		FILEGROWTH = 5MB
	)

Open in new window


and still got the same message about that FG0 not having any file associated with...
I am going to double check few things and even re-do the process if I can.
thx

JohnE
0
John EsraeloDatabase / SQL DeveloperAuthor Commented:
ouch.. I think I know what is missing..
I will be right back..

;)
0
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Awesome, it worked like a champ.
Dropped the, actually the right term is removed, the files, then recreated with the association to each filegroup.
use admin
go

/*
 alter database [admin] remove file fg0_file
 alter database [admin] remove file fg1_file
 alter database [admin] remove file fg2_file
 alter database [admin] remove file fg3_file
 alter database [admin] remove file fg4_file
 alter database [admin] remove file fg5_file
 alter database [admin] remove file fg6_file
 alter database [admin] remove file fg7_file
 alter database [admin] remove file fg8_file
 alter database [admin] remove file fg9_file

*/

ALTER DATABASE [Admin] ADD FILEGROUP FG0;
ALTER DATABASE [Admin] ADD FILEGROUP FG1;
ALTER DATABASE [Admin] ADD FILEGROUP FG2;
ALTER DATABASE [Admin] ADD FILEGROUP FG3;
ALTER DATABASE [Admin] ADD FILEGROUP FG4;
ALTER DATABASE [Admin] ADD FILEGROUP FG5;
ALTER DATABASE [Admin] ADD FILEGROUP FG6;
ALTER DATABASE [Admin] ADD FILEGROUP FG7;
ALTER DATABASE [Admin] ADD FILEGROUP FG8;
ALTER DATABASE [Admin] ADD FILEGROUP FG9;


alter database [admin] add file
	(
	    NAME = FG0_File,
		FILENAME = 'C:\DBA\DATA\FG0_File.ndf',
		SIZE = 5MB,
		MAXSIZE = 10MB,
		FILEGROWTH = 5MB
	) TO FileGroup FG0

Open in new window


Thank you very much for your help.
Learned something big today.

JohnE
0
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Thank you very much.. as usual, the assistance and the vast knowledge is amazing around this place..
thx

JohnE
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.