Sqlserver表分区

use TestSplitDB

--先创建好数据库
--1. 创建文件组
alter database TestSplitDB add filegroup testSplitGroup1
alter database TestSplitDB add filegroup testSplitGroup2
alter database TestSplitDB add filegroup testSplitGroup3
alter database TestSplitDB add filegroup testSplitGroup4
alter database TestSplitDB add filegroup testSplitGroup5

--2. 创建数据文件到文件组里面
alter database TestSplitDB add file
(name=N'Test1',filename=N'F:\work\FA\FDC\DataBase\Test\Test1.ndf',size=5Mb,filegrowth=5mb)
to filegroup testSplitGroup1

alter database TestSplitDB add file
(name=N'Test2',filename=N'F:\work\FA\FDC\DataBase\Test\Test2.ndf',size=5Mb,filegrowth=5mb)
to filegroup testSplitGroup2

alter database TestSplitDB add file
(name=N'Test3',filename=N'F:\work\FA\FDC\DataBase\Test\Test3.ndf',size=5Mb,filegrowth=5mb)
to filegroup testSplitGroup3

alter database TestSplitDB add file
(name=N'Test4',filename=N'F:\work\FA\FDC\DataBase\Test\Test4.ndf',size=5Mb,filegrowth=5mb)
to filegroup testSplitGroup4

alter database TestSplitDB add file
(name=N'Test5',filename=N'F:\work\FA\FDC\DataBase\Test\Test5.ndf',size=5Mb,filegrowth=5mb)
to filegroup testSplitGroup5

--3. 右键到要分区的表--- >> 存储 --- >> 创建分区 --- >>显示向导视图 --- >> 下一步 --- >> 下一步

--假如你选择的是int类型的列：那么你的分区可以指定为1--100W是一个分区，100W--200W是一个分区....
--假如你选择的是datatime类型：那么你的分区可以指定为：2019-01-01--2019-01-31一个分区...
--根据这样的列数据规则划分，那么在那个区间的数据，在插入数据库时就被指向那个分区存储下来

 

 

最后：执行创建分区形成的sql语句：

USE [TestSplitDB]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [testfuction](bigint) AS RANGE LEFT FOR VALUES (N'100', N'200', N'300', N'400', N'500')

CREATE PARTITION SCHEME [testarea] AS PARTITION [testfuction] TO ([PRIMARY],
[testSplitGroup1], [testSplitGroup2], [testSplitGroup3], [testSplitGroup4], [testSplitGroup5])
go

CREATE CLUSTERED INDEX [ClusteredIndex_on_testarea_636905853491577240] ON [dbo].[Test]
(
[DateTime]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [testarea]([DateTime])

DROP INDEX [ClusteredIndex_on_testarea_636905853491577240] ON [dbo].[Test]

COMMIT TRANSACTION