Partition--分区Demo
--=============================================================
--创建分区函数
--创建500分区,分区键按照1000依次递增
CREATE PARTITION FUNCTION [pf_Test](bigint) AS RANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000, 11000, 12000, 13000, 14000, 15000, 16000, 17000, 18000, 19000, 20000, 21000, 22000, 23000, 24000, 25000, 26000, 27000, 28000, 29000, 30000, 31000, 32000, 33000, 34000, 35000, 36000, 37000, 38000, 39000, 40000, 41000, 42000, 43000, 44000, 45000, 46000, 47000, 48000, 49000, 50000, 51000, 52000, 53000, 54000, 55000, 56000, 57000, 58000, 59000, 60000, 61000, 62000, 63000, 64000, 65000, 66000, 67000, 68000, 69000, 70000, 71000, 72000, 73000, 74000, 75000, 76000, 77000, 78000, 79000, 80000, 81000, 82000, 83000, 84000, 85000, 86000, 87000, 88000, 89000, 90000, 91000, 92000, 93000, 94000, 95000, 96000, 97000, 98000, 99000, 100000, 101000, 102000, 103000, 104000, 105000, 106000, 107000, 108000, 109000, 110000, 111000, 112000, 113000, 114000, 115000, 116000, 117000, 118000, 119000, 120000, 121000, 122000, 123000, 124000, 125000, 126000, 127000, 128000, 129000, 130000, 131000, 132000, 133000, 134000, 135000, 136000, 137000, 138000, 139000, 140000, 141000, 142000, 143000, 144000, 145000, 146000, 147000, 148000, 149000, 150000, 151000, 152000, 153000, 154000, 155000, 156000, 157000, 158000, 159000, 160000, 161000, 162000, 163000, 164000, 165000, 166000, 167000, 168000, 169000, 170000, 171000, 172000, 173000, 174000, 175000, 176000, 177000, 178000, 179000, 180000, 181000, 182000, 183000, 184000, 185000, 186000, 187000, 188000, 189000, 190000, 191000, 192000, 193000, 194000, 195000, 196000, 197000, 198000, 199000, 200000, 201000, 202000, 203000, 204000, 205000, 206000, 207000, 208000, 209000, 210000, 211000, 212000, 213000, 214000, 215000, 216000, 217000, 218000, 219000, 220000, 221000, 222000, 223000, 224000, 225000, 226000, 227000, 228000, 229000, 230000, 231000, 232000, 233000, 234000, 235000, 236000, 237000, 238000, 239000, 240000, 241000, 242000, 243000, 244000, 245000, 246000, 247000, 248000, 249000, 250000, 251000, 252000, 253000, 254000, 255000, 256000, 257000, 258000, 259000, 260000, 261000, 262000, 263000, 264000, 265000, 266000, 267000, 268000, 269000, 270000, 271000, 272000, 273000, 274000, 275000, 276000, 277000, 278000, 279000, 280000, 281000, 282000, 283000, 284000, 285000, 286000, 287000, 288000, 289000, 290000, 291000, 292000, 293000, 294000, 295000, 296000, 297000, 298000, 299000, 300000, 301000, 302000, 303000, 304000, 305000, 306000, 307000, 308000, 309000, 310000, 311000, 312000, 313000, 314000, 315000, 316000, 317000, 318000, 319000, 320000, 321000, 322000, 323000, 324000, 325000, 326000, 327000, 328000, 329000, 330000, 331000, 332000, 333000, 334000, 335000, 336000, 337000, 338000, 339000, 340000, 341000, 342000, 343000, 344000, 345000, 346000, 347000, 348000, 349000, 350000, 351000, 352000, 353000, 354000, 355000, 356000, 357000, 358000, 359000, 360000, 361000, 362000, 363000, 364000, 365000, 366000, 367000, 368000, 369000, 370000, 371000, 372000, 373000, 374000, 375000, 376000, 377000, 378000, 379000, 380000, 381000, 382000, 383000, 384000, 385000, 386000, 387000, 388000, 389000, 390000, 391000, 392000, 393000, 394000, 395000, 396000, 397000, 398000, 399000, 400000, 401000, 402000, 403000, 404000, 405000, 406000, 407000, 408000, 409000, 410000, 411000, 412000, 413000, 414000, 415000, 416000, 417000, 418000, 419000, 420000, 421000, 422000, 423000, 424000, 425000, 426000, 427000, 428000, 429000, 430000, 431000, 432000, 433000, 434000, 435000, 436000, 437000, 438000, 439000, 440000, 441000, 442000, 443000, 444000, 445000, 446000, 447000, 448000, 449000, 450000, 451000, 452000, 453000, 454000, 455000, 456000, 457000, 458000, 459000, 460000, 461000, 462000, 463000, 464000, 465000, 466000, 467000, 468000, 469000, 470000, 471000, 472000, 473000, 474000, 475000, 476000, 477000, 478000, 479000, 480000, 481000, 482000, 483000, 484000, 485000, 486000, 487000, 488000, 489000, 490000, 491000, 492000, 493000, 494000, 495000, 496000, 497000, 498000, 499000)
GO
--=============================================================
--创建分区模式,所有分区写入到 [PRIMARY] 文件组
CREATE PARTITION SCHEME [ps_Test] AS PARTITION [pf_Test] ALL TO ([PRIMARY]);
GO
--=============================================================
--创建表
CREATE TABLE [dbo].[TB1](
[C1] [int] IDENTITY(1,1) NOT NULL,
[C2] [nvarchar](200) NULL,
[C3] [nvarchar](200) NULL,
[PID] [bigint] NOT NULL,
)
--=============================================================
--创建聚簇索引,将表分区
CREATE UNIQUE CLUSTERED INDEX [CI_PID_CI] ON [dbo].[TB1]
(
[C1] ASC,
[PID] ASC
)ON [ps_Test]([PID])
GO
--=============================================================
--创建非聚簇索引,索引与表分区对齐
CREATE NONCLUSTERED INDEX [NI_C2] ON [dbo].[TB1]
(
[C2] ASC
)ON [ps_Test]([PID])
GO
--=============================================================
--插入数据
WITH Tmp AS
(
SELECT ROW_NUMBER()OVER(ORDER BY V1.object_id) AS RowId,
V1.name AS C2,
V1.name AS C3
FROM sys.all_views V1
CROSS JOIN sys.all_views V2
)
INSERT INTO dbo.TB1(C2,C3,PID)
SELECT C2,C3,RowId FROM Tmp
--=============================================================
--验证数据和分区
SELECT * FROM [dbo].[TB1]
WHERE PID<3000
--============================================================
--创建历史表,将PID 为0-1000的数据切换出去
BEGIN TRANSACTION
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[staging_TB1_P_0_1000](
[C1] [int] NOT NULL,
[C2] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[C3] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[PID] [bigint] NOT NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [staging_TB1_P_0_1000_CI_PID_CI] ON [dbo].[staging_TB1_P_0_1000]
(
[C1] ASC,
[PID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [staging_TB1_P_0_1000_NI_C2] ON [dbo].[staging_TB1_P_0_1000]
(
[C2] ASC
)
INCLUDE ( [PID]) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE [dbo].[staging_TB1_P_0_1000] WITH CHECK ADD CONSTRAINT [chk_staging_TB1_P_0_1000_partition_1] CHECK ([PID]<=N'1000')
ALTER TABLE [dbo].[staging_TB1_P_0_1000] CHECK CONSTRAINT [chk_staging_TB1_P_0_1000_partition_1]
ALTER TABLE [dbo].[TB1] SWITCH PARTITION 1 TO [dbo].[staging_TB1_P_0_1000]
COMMIT TRANSACTION
--============================================================
--验证数据
SELECT * FROM [dbo].[staging_TB1_P_0_1000]
SELECT * FROM [dbo].[TB1] WHERE PID<1000
--============================================================
--合并分区0-1000和1000-2000
ALTER PARTITION FUNCTION [pf_Test]()
MERGE RANGE (1000);
--============================================================
--为499000-500000创建分区
ALTER PARTITION FUNCTION [pf_Test]()
SPLIT RANGE (500000);
最新文章
- Apache Curator: Zookeeper客户端
- 《JavaScript权威指南》学习笔记 第四天 数组
- 使用jstack分析java程序cpu占用率过高
- IOS-UI-UILable
- CSS3 之 box-shadow
- fcitx-rime添加五笔/五笔拼音
- express之req res
- 通过编写一个简单的日志类库来加深了解C#的文件访问控制
- Android Studio添加aar依赖
- HTML5中input标签有用的新属性
- CSS 实现单、多行文本溢出显示省略号(…)
- chrome视频播放加速
- scrapy -->;CrawlSpider 介绍
- Linux下函数调用堆栈帧的详细解释【转】
- h5屏幕旋转的时间和样式的设置
- execl 导入
- protobuf3.5.1使用的简单例子
- Graphite grafana
- MySQL索引优化案例浅析
- 【转载】Oracle数据字典详解
热门文章
- Java--神奇的hashcode
- Swift 修改UITextField.Placeholder颜色
- vuex-- Vue.的中心化状态管理方案(vue 组件之间的通信简化机制)
- mongodb Java(八)
- 跟我学算法-SVM(支持向量机)
- jena读取和解析本体文件
- 怎样获取datagrid中编辑列combobox的value值与text值
- [iOS]UIScrollView嵌套UITableView,超出屏幕的cell点击不了问题
- Redis入门 - Windows环境搭建与第一个C# Sample
- 启动和关闭MySQL服务