?
Solved

MS SQL 2008 +:  TSQL:: Partitioning and Parallelism

Posted on 2012-03-27
5
Medium Priority
?
801 Views
Last Modified: 2012-03-27
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
0
Comment
Question by:John Esraelo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 7

Accepted Solution

by:
micropc1 earned 2000 total points
ID: 37775041
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
 

Author Comment

by:John Esraelo
ID: 37775073
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
 

Author Comment

by:John Esraelo
ID: 37775075
ouch.. I think I know what is missing..
I will be right back..

;)
0
 

Author Comment

by:John Esraelo
ID: 37775094
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
 

Author Closing Comment

by:John Esraelo
ID: 37775099
Thank you very much.. as usual, the assistance and the vast knowledge is amazing around this place..
thx

JohnE
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question