IF DB_ID('TestDB_PT') IS NOT NULL
   DROP DATABASE TestDB_PT;
GO
CREATE DATABASE TestDB_PT
ON
PRIMARY
(Name=TestDB_PT,
FILENAME='C:\DB\TestDB_PT\TestDB_PT.mdf',
SIZE=2MB,MAXSIZE=1024MB,FILEGROWTH=10MB
)
LOG ON
(
Name=TestDB_PT_LOG,
FILENAME='C:\DB\TestDB_PT\TestDB_PT_LOG.ldf',
SIZE=1MB,MAXSIZE=1024MB,FILEGROWTH=10MB
)
GO
USE TestDB_PT
GO

DECLARE @Count INT =4; -- 添加4个文件组
DECLARE @i INT =0;
DECLARE @SQL varchar(1000)='';
WHILE (@i<@Count)
BEGIN
SET @SQL='ALTER DATABASE TestDB_PT ADD FILEGROUP FG'+convert(varchar(10),@i+1)+';'
Exec (@SQL);
SET @[email protected]+1;
END
-- 添加文件到文件组
SET @i =0;
SET @SQL =''
WHILE (@i<@Count)
BEGIN
SET @SQL='ALTER DATABASE TestDB_PT ADD FILE( NAME=FG'+CONVERT(VARCHAR(10),@i+1)+'_Data1,FILENAME=''C:\DB\TestDB_PT\FG'+CONVERT(VARCHAR(10),@i+1)+'_Data1.ndf'' ,SIZE=1MB,MAXSIZE=100MB,FILEGROWTH=1MB ) TO FILEGROUP FG'+CONVERT(VARCHAR(10),@i+1)
Exec (@SQL)
SET @[email protected]+1;
END

CREATE PARTITION FUNCTION PFMonthly(int)
AS RANGE RIGHT –- 还可以是RANGE LEFT.详细了解RANGE RIGHT就足够了
FOR VALUES(20130601,20130701,20130801)

CREATE PARTITION SCHEME PSMonthly
AS PARTITION PFMonthly -- 分区函数
TO (FG1,FG2,FG3,FG4) -- 对应文件组个数为分区函数范围值加1

CREATE TABLE [dbo].[TB](
   [DateKey] [int] NOT NULL,
   [number] [int]  NULL
) ON PSMonthly([DateKey]) -- 此处对应分区方案和分区列

-- 创建切换时需要的临时表
IF OBJECT_ID('TestDB_PT.dbo.Stage_TB') IS NOT NULL
DROP TABLE TestDB_PT.dbo.Stage_TB
GO
CREATE TABLE [dbo].[Stage_TB](
   [DateKey] [int] NOT NULL,
   [number] [int]  NULL
) ON FG1 --必须是将要加载的数据对应的文件组

-- 添加DateKey的约束。这个必须添加，否则在切换的时候会报语法错误。这个约束的目的是防止切换的时候需要将临时表中的数据切换到不同的分区。
ALTER TABLE [dbo].[Stage_TB]
ADD CONSTRAINT CK_Stage_TB_DateKey CHECK(DateKey>= 20130501and DateKey<20130601)
-- 添加数据到Stage_TB
insert into dbo.Stage_TB
SELECT Convert(varchar(20), Dateadd(DAY,number,'2013-05-21'),112 ) DateKey
,number
FROM master.dbo.spt_values
where type='p'
and Dateadd(DAY,number,'2013-05-21') <'20130601'           
--  通过switch语句做切换
ALTER TABLE  Stage_TB
SWITCH  TO TB PARTITION 1 -- 这个地方的分区号在前面的查询中能看到

-- 查询分区表中实际数据情况
select $partition.PFMonthly(DateKey) as [Partition#]
,count(*) RowCnt
,Min(DateKey) AS MinDate
,Max(DateKey) AS MaxDate
from TB
group by $partition.PFMonthly(DateKey)
order by [Partition#]    
--  删除临时表
DROP TABLE Stage_TB

--   删除20130501到20130531的数据
-- 创建切换时需要的临时表
IF OBJECT_ID('TestDB_PT.dbo.Stage_TB') IS NOT NULL
DROP TABLE TestDB_PT.dbo.Stage_TB
GO
CREATE TABLE [dbo].[Stage_TB](
   [DateKey] [int] NOT NULL,
   [number] [int]  NULL
) ON FG1 --必须是将要加载的数据对应的文件组
--  通过switch语句，切换数据到临时表
ALTER TABLE  TB
SWITCH PARTITION 1 TO Stage_TB  -- 这个地方的分区号在前面的查询中能看到
-- 查看对应的分区值
SELECT 
     SPS.name AS PartitionSchemeName
   , CASE WHEN SDD.destination_id <= SPF.fanout THEN SDD.destination_id
        ELSE NULL END AS PartitionID
   , SPF.name AS PartitionFunctionName
   , SPRV.value AS BoundaryValue
   , CASE WHEN SDD.destination_id > SPF.fanout THEN 1 
        ELSE 0 END AS NextUsed
   , SF.name AS FileGroup
FROM sys.partition_schemes AS SPS
JOIN sys.partition_functions AS SPF 
   ON SPS.function_id = SPF.function_id
JOIN sys.destination_data_spaces AS SDD 
   ON SDD.partition_scheme_id = SPS.data_space_id
JOIN sys.filegroups AS SF 
   ON SF.data_space_id = SDD.data_space_id
LEFT JOIN sys.partition_range_values AS SPRV 
   ON SPRV.function_id = SPF.function_id
   AND SDD.destination_id = 
CASE WHEN SPF.boundary_value_on_right = 0 THEN SPRV.boundary_id
   ELSE SPRV.boundary_id + 1 END 
WHERE SPS.name = 'PSMonthly';
-- 从分区函数中去掉对应值
ALTER PARTITION FUNCTION PFMonthly()
MERGE RANGE(20130601)
-- 删除临时表
DROP TABLE Stage_TB