sql server 分区表数据迁移中switch的应用
-- create partition function
CREATE PARTITION FUNCTION partRange1 (INT)
AS RANGE LEFT FOR VALUES (10, 20, 30) ;
GO
-- create partition scheme
CREATE PARTITION SCHEME partScheme1
AS PARTITION partRange1
ALL TO ([PRIMARY]) ;
GO
-- create table that uses this partitioning scheme
CREATE TABLE partTable (col1 INT, col2 VARCHAR(20))
ON partScheme1 (col1) ;
GO
--查询分区情况
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('partTable')
GO
-- insert some sample data
INSERT INTO partTable (col1, col2) VALUES (5, 'partTable')
INSERT INTO partTable (col1, col2) VALUES (6, 'partTable')
INSERT INTO partTable (col1, col2) VALUES (7, 'partTable')
GO
-- select the data
SELECT * FROM partTable
go
-- switch in
CREATE TABLE newPartTable (col1 INT CHECK (col1 > 30 AND col1 <= 40 AND col1 IS NOT NULL),
col2 VARCHAR(20))
GO
-- insert some sample data into new table
INSERT INTO newPartTable (col1, col2) VALUES (31, 'newPartTable')
INSERT INTO newPartTable (col1, col2) VALUES (32, 'newPartTable')
INSERT INTO newPartTable (col1, col2) VALUES (33, 'newPartTable')
-- select the data
SELECT * FROM partTable
SELECT * FROM newPartTable
go
-- make the switch
ALTER TABLE newPartTable SWITCH TO partTable PARTITION 4;
GO
-- select the data
SELECT * FROM partTable
SELECT * FROM newPartTable
go
-- switch out
CREATE TABLE nonPartTable (col1 INT, col2 VARCHAR(20))
ON [primary] ;
GO
-- make the switch
ALTER TABLE partTable SWITCH PARTITION 1 TO nonPartTable ;
GO
-- select the data
SELECT * FROM partTable
SELECT * FROM nonPartTable
