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);