data partitioning

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  September 26, 2012 at 2:55 am

  #267244

  Hi All,

  I am new sql server partitioning. I have tried implementing the range partiotioning.

  use master

  go

  create database demo

  go

  USE [master]

  GO

  ALTER DATABASE [demo] ADD FILEGROUP [fg1]

  GO

  ALTER DATABASE [demo] ADD FILEGROUP [fg2]

  GO

  ALTER DATABASE [demo] ADD FILEGROUP [fg3]

  GO

  ALTER DATABASE [demo] ADD FILEGROUP [fg4]

  GO

  USE [master]

  GO

  ALTER 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]

  GO

  ALTER 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]

  GO

  ALTER 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]

  GO

  ALTER 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 demo

  go

  drop 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 all

  select 102,'B','20040602'

  union all

  select 103,'C','20040701'

  union all

  select 104,'D','20040702'

  union all

  select 105,'E','20040801'

  union all

  select 106,'F','20040802'

  union all

  select 107,'G','20040901'

  union all

  select 108,'H','20040905'

  union all

  select 109,'I','20040101' -- expecting to fall in Primary filegroup -Jan data

  union all

  select 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 master

  go

  backup database demo

  to 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.