SQLSERVER2008R2创建分区表、转换分区表、交换数据

分区

表和索引的分区可以不同，但聚集索引必须和表采用相同的分区方案，必须采用相同的分区列，也就是说，如果表先创建了分区表，那么在创建分区聚集索引时，该索引必须是表的分区列，而且索引也必须按照表的分区列进行分区。

图形化界面在表上创建分区：

表-右键-存储-创建分区-根据提示创建分区函数和分区方案即可。

需要首先添加好文件组和文件，最好一个文件组对应一个文件。

管理分区把某个分区中的数据移除到目标表：

1.表-右键-存储-管理分区-先选择为分区切换创建临时表-选择要将那个边界值的分区数据移出-立即运行即可。

2.表-右键-存储-管理分区-管理可调窗口应用场景中的分区数据-上方选择切出-下边可以选择新建表或者先前创建的表-立即运行即可

-------创建一个分区表

T-SQL创建分区表;

1.创建分区函数

CREATEPARTITIONFUNCTIONTESTFUNC(INT)

ASRANGELEFT        --指定边界值包含在左边的分区还是右边的分区

FORVALUES('10','20')  - --分区的边界值

GO

SELECT*FROMsys.partition_functions   --- 查看分区表是否创建成功

2.创建分区方案

CREATEPARTITIONSCHEMETESTSCHEME

AS

PARTITIONTESTFUNC    --要关联上一步骤创建的分区函数

TO

(filegroup1,filegroup2,filegroup3)   --把分区映射到不同的文件组，文件组要事先创建好

SELECT * FROM sys.partition_schemes  ---查看已创建的分区构架

创建分区表   ----只能在创建表时进行分区

CREATETABLE[dbo].[TESTPARTI](

      [id][int]NOTNULL,

      [name][varchar](20)NULL

)ONTESTSCHEME(ID)    --指定分区方案和分区列

---向表中插入测试数据

3.查看各分区中的数据

SELECT*FROMTEST0509

WHERE$PARTITION.TESTFUNC(ID)=2    --此处2是分区

select$partition.TESTFUNC(20)--查询某一条数据存储在哪个分区，此处20是分区列的数据

SELECT$PARTITION.FUNC_DATE(KFRQ)ASID,COUNT(*)ASCOUNT      --查看各个分区有多少条记录

FROMTB_CIS_PRESCRIPTION_DETAIL0510GROUPBY$PARTITION.FUNC_DATE(KFRQ)

4.修改分区函数添加一个分区

ALTERPARTITIONSCHEMETESTSCHEME    --首先需要新增一个文件组，然后再使用该语句把文件组添加到分区方案中留给新增的分区使用

NEXTUSEDFILEGROUP4    --使用next used标记该文件组用于下一个新增的分区

GO

ALTERPARTITIONFUNCTIONTESTFUNC()    ---修改分区函数，新增边界值，新增分区，使用上一步骤加入的文件组

SPLITRANGE('5')

5.使用MERGE删除一个边界值删除分区

ALTERPARTITIONFUNCTIONTESTFUNC()

MERGERANGE('5')    ---删除边界值为5的分区

6.使用SWITCH移动数据

-----把分区表中的数据切换到另一分区表

两表要有相同的字段，字段属性，主键，索引，分区方案，这里是为了保证数据只能在同一个文件组内移动

若表上有索引，不管是聚集索引还是非聚集索引，索引必须使用和表一样的分区方案,分区列进行分区，叫做表与索引对齐，否则不能进行切换。

alter table orders switch partition 1 to ordersHistory partition 1

在已分区的表上创建索引（分区索引）时，应该注意以下事项：

唯一索引

建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此限制将使SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用DML触发器，而不是强制实现唯一性。

非唯一索引
对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下SQL Server 将在聚集索引列中添加分区依据列。

对非唯一的非聚集索引进行分区时，默认情况下SQL Server 将分区依据列添加为索引的包含性列，以确保索引与基表对齐，若果索引中已经存在分区依据列，SQL Server 将不会像索引中添加分区依据列。

--把分区表中数据移动到普通表，普通表要满足一下要求

• 字段数量相同，对应位置的字段相同
• 相同位置的字段要有相同的属性，相同的类型，相同的聚集索引、主键
• 两个表在一个文件组中

CREATETABLE[dbo].TESTSEXP   ---

(

      [id][int]NOTNULL,

      [name][varchar](20)NULL

)

 ONFILEGROUP4  ---目标表必须和源表在同一文件组

ALTERTABLEdbo.TESTPARTI

SWITCH PARTITION1TOTESTSEXP    ---把分区1中的数据移出到目标表

--从普通表复制数据到分区表

注意的是要先将分区表中的索引删除，即便普通表中存在跟分区表中相同的索引

alter table <普通表名> switch to 分区表 partition 1 

----将普通表转换为分区表 

1.

在分区依据列上创建一个聚集索引，并在聚集索引上使用分区方案即可。

若该普通表有主键和聚集索引，且聚集索引不是分区列，就要把该主键删除，重新创建该主键，但把该主键创建为非聚集索引，接着在分区列上创建聚集索引即可。

alter table 普通表名     --删除主键

drop constraint 主键名

alter table 普通表名     --重新创建主键，但创建为非聚集索引

add constraint 主键名 primary key nonclustered

( 主键字段 asc) on primary

create clustered index 索引名    --在分区列上创建聚集索引把普通表转换为分区表，此处的索引列要和分区列是同一列。

on 普通表名（字段名）

on 分区方案名（分区列名）

若主键是分区所在列，那么可以删除主键重新在该列创建聚集索引并分区即可。

• CREATE CLUSTERED INDEX CT_Sale1 ON Sale1([SaleTime])  
•     WITH ( DROP_EXISTING = ON)      --如果该索引存在就删除
• ON 分区方案名（分区列名）  

2.创建一个新的分区表，把旧表中的数据导入到新的分区表

-----将一个分区表转换为非分区表

可以通过使用删除分区的方式将表中所有分区删除，变成一个分区，从而转换成非分区表，虽然该表依然是分区表，但和普通表没有什么区别。

ALTERPARTITIONFUNCTIONTESTFUNC()

MERGERANGE('10')

ALTERPARTITIONFUNCTIONTESTFUNC()

MERGERANGE('20')

若是通过创建分区索引的方法将普通表转换成的分区表，除了删除分区来转换为普通表外，还可以通过删除分区索引转换为普通表。

1.删除分区索引

2.在原来字段算上重建一个索引

• CREATE CLUSTERED INDEX CT_Sale1 ON Sale1([SaleTime])  
•     WITH ( DROP_EXISTING = ON)      --如果该索引存在就删除
• ON [PRIMARY]