快速删除分区表，某个区的数据

USE [master]
GO
--我们创建包含PRIMARY分区在内一共3分区的数据库
CREATE DATABASE [db_partition_test]
ON PRIMARY
(
NAME = N'db_partition_test',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data/db_partition_test.mdf'
),
FILEGROUP [FG1]
(
NAME = N'db_partition_test_fg1_1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data/db_partition_test_fg1_1.ndf'
),
FILEGROUP [FG2]
(
NAME = N'db_partition_test_fg2_1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data/db_partition_test_fg2_1.ndf'
)
LOG ON
(
NAME = N'db_partition_test_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data/db_partition_test_log.ldf'
)
GO
USE [db_partition_test]
GO
--创建分区函数，分区范围为 id<=100 | 100<id<=200 | id>200
CREATE PARTITION FUNCTION [id_range_pf_1](int) AS RANGE LEFT FOR VALUES (100, 200)
GO
--创建分区方案 ,分区一到PRIMARY,分区二到FG1,分区三到FG2
CREATE PARTITION SCHEME [id_range_ps_1] AS PARTITION [id_range_pf_1] TO ([PRIMARY],FG1,FG2);
GO
--创建分区表
CREATE TABLE dbo.lovesql
(
ID INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL PRIMARY KEY,
Col Uniqueidentifier DEFAULT(NEWID())
) ON id_range_ps_1(ID)

--插入测试数据 300条
INSERT INTO dbo.lovesql DEFAULT VALUES
GO 300
--查看表分区以后的分区行数
SELECT $PARTITION.id_range_pf_1(ID) [PartitionNum],COUNT(1) [PartitionRowCount]
FROM dbo.lovesql
GROUP BY $PARTITION.id_range_pf_1(ID)
ORDER BY [PartitionNum]
--输入结果，每个分区100条数据，分区正确！
--PartitionNum PartitionRowCount
-------------- -----------------
--1 100
--2 100
--3 100
--现在开始建立复制，首先建立目标数据repl_db_partition_test
USE [master];
GO
CREATE DATABASE [repl_db_partition_test]
ON PRIMARY
(
NAME = N'repl_db_partition_test',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data/repl_db_partition_test.mdf'
),
FILEGROUP [FG1]
(
NAME = N'repl_db_partition_test_fg1_1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data/repl_db_partition_test_fg1_1.ndf'
),
FILEGROUP [FG2]
(
NAME = N'repl_db_partition_test_fg2_1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data/repl_db_partition_test_fg2_1.ndf'
)
LOG ON
(
NAME = N'repl_db_partition_test_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data/repl_db_partition_test_log.ldf'
)
GO

USE db_partition_test;
GO
CREATE TABLE dbo.temp_lovesql_primary
(
ID INT PRIMARY KEY,
Col Uniqueidentifier
) ON [PRIMARY]

USE repl_db_partition_test;
GO
--同样的在订阅库创建临时表
CREATE TABLE dbo.temp_lovesql_primary
(
ID INT PRIMARY KEY,
Col Uniqueidentifier
) ON [PRIMARY]
--切换分区1到临时比哦啊
USE db_partition_test;
GO
ALTER TABLE dbo.lovesql SWITCH PARTITION 1 TO dbo.temp_lovesql_primary
--查看表分区以后的分区行数
SELECT $PARTITION.id_range_pf_1(ID) [PartitionNum],COUNT(1) [PartitionRowCount]
FROM dbo.lovesql
GROUP BY $PARTITION.id_range_pf_1(ID)
ORDER BY [PartitionNum]
--PartitionNum PartitionRowCount
-------------- -----------------
--2 100
--3 100
truncate table
--恭喜,测试成功,接下来,对两边同时TRUNCATE TABLE 就好了

 

转载于:https://www.cnblogs.com/xiangyazi520/archive/2012/09/06/2673512.html