Solved

MS SQL 2008 +:  TSQL:: Partitioning and Parallelism

Posted on 2012-03-27
5
769 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
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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL convert date to string 4 34
Negative isnull? 3 14
SQL Availablity Groups List 2 7
shrink table after huge delete 2 13
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 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