[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS SQL 2008 +:  TSQL:: Partitioning and Parallelism

Posted on 2012-03-27
5
Medium Priority
?
833 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

656 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