Ellie's blog

/*****************************************************************/
-- sample code 1
/*****************************************************************/

Create database Test_Partition
GO

use Test_Partition
GO

-- Create Partiton Fuctnion
create partition function PF_Nabk_FromDate(datetime2(0))
as
range right for values (
'2012-01-01',
'2012-02-01',
'2012-03-01',
'2012-04-01'
)
Go

-- Create Partition Scheme
create partition SCHEME PS_NABK
as
partition PF_Nabk_FromDate
all to ([primary])
go

-- Create partition table
CREATE TABLE [dbo].[PTNABK](C1 datetime2(0))
on PS_NABK (C1)

insert into PTNABK values('2012/01/03')
insert into PTNABK values('2012/02/03')
insert into PTNABK values('2012/03/03')
insert into PTNABK values('2012/04/03')
insert into PTNABK values('2012/05/03')

-- Query
select PARTITION_Nm = $PARTITION.PF_Nabk_FromDate(C1)
,rows = COUNT(1)
,minval = MIN(C1)
,maxval = MAX(c1)
from dbo.PTNABK
group by $PARTITION.PF_Nabk_FromDate(C1)
order by PARTITION_Nm

use master
go
drop database Test_Partition
go

/*****************************************************************/
-- sample code 2
/*****************************************************************/

CREATE DATABASE [test_partition] ON  PRIMARY
( NAME = N'test_partition', FILENAME = N'c:\temp\test_partition.mdf' ),
 FILEGROUP [test1fg]
( NAME = N'test1fg', FILENAME = N'c:\temp\test1fg.ndf'),
 FILEGROUP [test2fg]
( NAME = N'test2fg', FILENAME = N'c:\temp\test2fg.ndf'),
 FILEGROUP [test3fg]
( NAME = N'test3fg', FILENAME = N'c:\temp\test3fg.ndf'),
 FILEGROUP [test4fg]
( NAME = N'test4fg', FILENAME = N'c:\temp\test4fg.ndf' )
 LOG ON
( NAME = N'test_partition_log', FILENAME = N'c:\temp\test_partition_log.ldf' )
go

use [test_partition]
go

CREATE PARTITION FUNCTION myRangePF1 (int)
    AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    TO (test1fg, test2fg, test3fg, test4fg) ;
GO

CREATE TABLE PartitionTable (col1 int, col2 char(10))
    ON myRangePS1 (col1) ;
GO

Insert into  PartitionTable Values(2,'二'),(50,'五十'),(99,'九九'),(32,'三二'),(993,'九九三')
Insert into  PartitionTable Values(0,'零'),(1001,'1001')

select PARTITION_Nm = $PARTITION.myRangePF1(col1)
,rows = COUNT(1)
,minval = MIN(col1)
,maxval = MAX(col1)
from dbo.PartitionTable
group by $PARTITION.myRangePF1(col1)
order by PARTITION_Nm

-- swithch
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

Insert into  PartitionTable Values(57,'57')
 
select * from PartitionTable
select * from NonPartitionTable

use master
go
drop database Test_Partition
go

 

--select * From sys.partition_functions
--select * from sys.partition_schemes