Code
-- ============================
-- About Table/Index Partition
-- Author:Terry (浪客)
-- Location:Beijing (北京)
-- Date:2009-11-5
-- ============================
IF DB_ID('DBPartition') IS NOT NULL
DROP DATABASE DBPartition;
GO
CREATE DATABASE DBPartition
ON PRIMARY
(
NAME=DBPartition_data,
FILENAME='c:\DBPartition_data.mdf'
),
FILEGROUP FG1
(
NAME=DBPartition_data_FG1_1,
FILENAME='c:\DBPartition_data_1.ndf'
),
(
NAME=DBPartition_data_FG1_2,
FILENAME='c:\DBPartition_data_2.ndf'
),
FILEGROUP FG2
(
NAME=DBPartition_data_FG1_3,
FILENAME='c:\DBPartition_data_3.ndf'
)
LOG ON
(
NAME=DBPartition_log,
FILENAME='c:\DBPartition_log.ldf'
)
GO
USE DBPartition;
IF OBJECT_ID('dbo.T') IS NOT NULL
DROP TABLE dbo.T
GO
--Test result
--SELECT * FROM dbo.T
IF EXISTS(SELECT 1 FROM sys.partition_functions WHERE [name]=N'myRangePF1')
DROP PARTITION FUNCTION myRangePF1;
GO
CREATE PARTITION FUNCTION myRangePF1(INT) AS
RANGE LEFT FOR VALUES(100,300,500);
GO
IF EXISTS(SELECT 1 FROM sys.partition_schemes WHERE [name]=N'myRangePF1')
DROP PARTITION SCHEME myRangePF1;
GO
--It canuse a error because of a no existing filegroup FG3
CREATE PARTITION SCHEME myRangePS AS PARTITION myRangePF1
TO ([PRIMARY],FG1,FG2,FG3);
GO
ALTER DATABASE DBPartition ADD FILEGROUP FG3
GO
ALTER DATABASE DBPartition ADD FILE
(
NAME=DBPartition_data_FG1_4,
FILENAME='c:\DBPartition_data_4.ndf'
)
TO FILEGROUP FG3;
GO
ALTER DATABASE DBPartition ADD FILEGROUP FG4
GO
ALTER DATABASE DBPartition ADD FILE
(
NAME=DBPartition_data_FG1_5,
FILENAME='c:\DBPartition_data_5.ndf'
)
TO FILEGROUP FG4;
GO
IF EXISTS(SELECT 1 FROM sys.partition_schemes WHERE [name]=N'myRangePF1')
DROP PARTITION SCHEME myRangePF1;
GO
--It canuse a error because of a no existing filegroup FG3
CREATE PARTITION SCHEME myRangePS AS PARTITION myRangePF1
TO ([PRIMARY],FG1,FG2,FG3);
GO
ALTER PARTITION SCHEME myRangePS NEXT USED FG4
GO
CREATE TABLE dbo.T
(ID INT PRIMARY KEY) ON myRangePS(ID);
DECLARE @I INT;
SET @I=1;
INSERT INTO dbo.T SELECT @I;
WHILE @I*2<=1000
BEGIN
INSERT INTO dbo.T SELECT @I+ID FROM dbo.T
SET @I=@I*2;
END
INSERT INTO dbo.T SELECT @I+ID FROM dbo.T WHERE @I+ID<=1000
CREATE TABLE dbo.T1(ID INT PRIMARY KEY)
ALTER TABLE dbo.T SWITCH PARTITION 1 TO dbo.T1 PARTITION 1
ALTER TABLE dbo.T1 DROP CONSTRAINT [PK__T1__03317E3D] WITH(ONLINE=ON)
ALTER TABLE dbo.T1 ADD CONSTRAINT [PK_T1_ID] PRIMARY KEY CLUSTERED (ID) ON myRangePS(ID)
SELECT * FROM dbo.T1
-- ============================
-- About Table/Index Partition
-- Author:Terry (浪客)
-- Location:Beijing (北京)
-- Date:2009-11-5
-- ============================
IF DB_ID('DBPartition') IS NOT NULL
DROP DATABASE DBPartition;
GO
CREATE DATABASE DBPartition
ON PRIMARY
(
NAME=DBPartition_data,
FILENAME='c:\DBPartition_data.mdf'
),
FILEGROUP FG1
(
NAME=DBPartition_data_FG1_1,
FILENAME='c:\DBPartition_data_1.ndf'
),
(
NAME=DBPartition_data_FG1_2,
FILENAME='c:\DBPartition_data_2.ndf'
),
FILEGROUP FG2
(
NAME=DBPartition_data_FG1_3,
FILENAME='c:\DBPartition_data_3.ndf'
)
LOG ON
(
NAME=DBPartition_log,
FILENAME='c:\DBPartition_log.ldf'
)
GO
USE DBPartition;
IF OBJECT_ID('dbo.T') IS NOT NULL
DROP TABLE dbo.T
GO
--Test result
--SELECT * FROM dbo.T
IF EXISTS(SELECT 1 FROM sys.partition_functions WHERE [name]=N'myRangePF1')
DROP PARTITION FUNCTION myRangePF1;
GO
CREATE PARTITION FUNCTION myRangePF1(INT) AS
RANGE LEFT FOR VALUES(100,300,500);
GO
IF EXISTS(SELECT 1 FROM sys.partition_schemes WHERE [name]=N'myRangePF1')
DROP PARTITION SCHEME myRangePF1;
GO
--It canuse a error because of a no existing filegroup FG3
CREATE PARTITION SCHEME myRangePS AS PARTITION myRangePF1
TO ([PRIMARY],FG1,FG2,FG3);
GO
ALTER DATABASE DBPartition ADD FILEGROUP FG3
GO
ALTER DATABASE DBPartition ADD FILE
(
NAME=DBPartition_data_FG1_4,
FILENAME='c:\DBPartition_data_4.ndf'
)
TO FILEGROUP FG3;
GO
ALTER DATABASE DBPartition ADD FILEGROUP FG4
GO
ALTER DATABASE DBPartition ADD FILE
(
NAME=DBPartition_data_FG1_5,
FILENAME='c:\DBPartition_data_5.ndf'
)
TO FILEGROUP FG4;
GO
IF EXISTS(SELECT 1 FROM sys.partition_schemes WHERE [name]=N'myRangePF1')
DROP PARTITION SCHEME myRangePF1;
GO
--It canuse a error because of a no existing filegroup FG3
CREATE PARTITION SCHEME myRangePS AS PARTITION myRangePF1
TO ([PRIMARY],FG1,FG2,FG3);
GO
ALTER PARTITION SCHEME myRangePS NEXT USED FG4
GO
CREATE TABLE dbo.T
(ID INT PRIMARY KEY) ON myRangePS(ID);
DECLARE @I INT;
SET @I=1;
INSERT INTO dbo.T SELECT @I;
WHILE @I*2<=1000
BEGIN
INSERT INTO dbo.T SELECT @I+ID FROM dbo.T
SET @I=@I*2;
END
INSERT INTO dbo.T SELECT @I+ID FROM dbo.T WHERE @I+ID<=1000
CREATE TABLE dbo.T1(ID INT PRIMARY KEY)
ALTER TABLE dbo.T SWITCH PARTITION 1 TO dbo.T1 PARTITION 1
ALTER TABLE dbo.T1 DROP CONSTRAINT [PK__T1__03317E3D] WITH(ONLINE=ON)
ALTER TABLE dbo.T1 ADD CONSTRAINT [PK_T1_ID] PRIMARY KEY CLUSTERED (ID) ON myRangePS(ID)
SELECT * FROM dbo.T1