sqlserver分区

SQL表分区与索引创建

最新推荐文章于 2024-09-13 14:25:20 发布

原创最新推荐文章于 2024-09-13 14:25:20 发布 · 154 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver

SQLServer 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个SQL示例，展示了如何创建分区表、插入数据、创建分区函数及方案，并建立非聚集索引。此外，还演示了如何通过分区方案来分配数据到不同的文件组。

--表分区

create table splitTable(id int primary key identity, name varchar(100),descript varchar(300),createTime datetime);

insert into splitTable(name, descript,createTime) values('分区表测试','分区表测试测试','2017-01-12');

insert into splitTable select name,descript,createTime from splitTable;

select * from splitTable;

create partition function testPartition(Datetime) as range right for values('2017-01-09', '2017-01-11');

select * from sys.filegroups;

select * from sys.partition_functions

create partition scheme testPartition_scheme as partition testPartition
to ([FileGroup1],[Primary],[FileGroup2]);

CREATE NONCLUSTERED INDEX [sniperTable_Index_new] ON [dbo].[splitTable]
(
createTime ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [testPartition_scheme]([createTime])
GO

select from splitTable where $PARTITION.testPartition(createTime)=3

truncate table splitTable;