Solved

MS SQL 2008 +:  TSQL:: Partitioning and Parallelism

Posted on 2012-03-27
5
750 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
  • 4
5 Comments
 
LVL 7

Accepted Solution

by:
micropc1 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
ouch.. I think I know what is missing..
I will be right back..

;)
0
 

Author Comment

by:John Esraelo
Comment Utility
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
Comment Utility
Thank you very much.. as usual, the assistance and the vast knowledge is amazing around this place..
thx

JohnE
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now