InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 07:37:29 GMT20http://www.sqlservercentral.com/Forums/Topic1364506-1550-1.aspxHi All,I am new sql server partitioning. I have tried implementing the range partiotioning.use master go create database demogoUSE [master]GOALTER DATABASE [demo] ADD FILEGROUP [fg1]GOALTER DATABASE [demo] ADD FILEGROUP [fg2]GOALTER DATABASE [demo] ADD FILEGROUP [fg3]GOALTER DATABASE [demo] ADD FILEGROUP [fg4]GOUSE [master]GOALTER DATABASE [demo] ADD FILE ( NAME = N'fg1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.GANESH\MSSQL\DATA\fg1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg1]GOALTER DATABASE [demo] ADD FILE ( NAME = N'fg2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.GANESH\MSSQL\DATA\fg2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2]GOALTER DATABASE [demo] ADD FILE ( NAME = N'fg3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.GANESH\MSSQL\DATA\fg3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg3]GOALTER DATABASE [demo] ADD FILE ( NAME = N'fg4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.GANESH\MSSQL\DATA\fg4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg4]GO create partition function demo_partition_function(datetime) --- specifying my partition function over datetime datatype . as range right for values ('20040601', --june 2004 '20040701', -- jul 2004 '20040801', -- aug 2004 '20040901') -- sep 2004 go create partition scheme demo_partition_scheme as partition demo_partition_function to ([Primary],[fg1],[fg2],[fg3],[fg4]) --map the localigal boundaries to their physical locations go use demogodrop table sales go create table sales(id int not null, name varchar(100), doj datetime not null ) go alter table sales add constraint pk_sales primary key clustered(doj,id) on demo_partition_scheme (doj) go insert into sales select 101,'A','20040601' union allselect 102,'B','20040602' union allselect 103,'C','20040701' union allselect 104,'D','20040702' union allselect 105,'E','20040801' union allselect 106,'F','20040802' union allselect 107,'G','20040901' union allselect 108,'H','20040905' union all select 109,'I','20040101' -- expecting to fall in Primary filegroup -Jan data union allselect 110,'J','20040301' -- expecting to fall in Primary filegroup -Mar data select $partition.demo_partition_function(doj) as [Partiton Number], MIN(doj) as [Min date], MAX(doj) as [Max date], COUNT(*) as [Rows in Partiton]from dbo.sales group by $partition.demo_partition_function(doj)order by [Partiton Number]go use mastergo backup database demoto disk = 'd:\demopartitoning.bak'go -- now i need to alter partition function to define my boundary for Jan and March month -- i will be adding two new filegroups fg5,fg6, -- now my question is how would be my alter partition fuction ?-- Do i need to do it from scratch dropping everything or can we alter the function and move the data to corresponding file groups?Any help would be appreciated. Thanks in Advance. Wed, 26 Sep 2012 02:55:04 GMTOracle_91