--添加文件组和文件，确定目录[c:/SQLDataPart]/[d:/SQLDataPart]已存在
/*
if exists(select 1 from sys.sysfiles where name='Exam99')
alter database EventExam remove file Exam99
if exists(select 1 from sys.filegroups where name='FG_Exam99')
alter database EventExam remove filegroup FG_Exam99
go
select * from sys.filegroups
select * from sys.sysfiles
*/
alter database EventExam add filegroup FG_Exam1
alter database EventExam add file(name='Exam1',filename='c:/SQLDataPart/Exam1.ndf',size=5mb,filegrowth=5mb) to filegroup FG_Exam1
alter database EventExam add filegroup FG_Exam2
alter database EventExam add file(name='Exam2',filename='d:/SQLDataPart/Exam2.ndf',size=5mb,filegrowth=5mb) to filegroup FG_Exam2
alter database EventExam add filegroup FG_Exam3
alter database EventExam add file(name='Exam3',filename='c:/SQLDataPart/Exam3.ndf',size=5mb,filegrowth=5mb) to filegroup FG_Exam3
alter database EventExam add filegroup FG_Exam4
alter database EventExam add file(name='Exam4',filename='d:/SQLDataPart/Exam4.ndf',size=5mb,filegrowth=5mb) to filegroup FG_Exam4
alter database EventExam add filegroup FG_Exam99
alter database EventExam add file(name='Exam99',filename='d:/SQLDataPart/Exam99.ndf',size=1mb,filegrowth=1mb) to filegroup FG_Exam99
go
use EventExam;
go
--创建分区函数和架构
if exists(Select 1 from sys.partition_schemes where name='PS_Exam')
drop partition scheme PS_Exam
go
if exists(Select 1 from sys.partition_functions where name='PF_Exam')
drop partition function PF_Exam
go
create partition function PF_Exam(int)
as range left
for values(0,40,80,120,160,200,250,300,350,400,450,800,1500)
go
create partition scheme PS_Exam
as partition PF_Exam
to([Primary],FG_Exam1,FG_Exam1,FG_Exam1,FG_Exam2,FG_Exam2,FG_Exam2,
FG_Exam3,FG_Exam3,FG_Exam3,FG_Exam4,FG_Exam4,FG_Exam4,FG_Exam99,FG_Exam99)
go
--************************<CHOOSE1重建聚集索引>*****************************************
/*
select * from sys.sysindexes where OBJECT_NAME(id)='Exam'
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('Exam')
select COUNT(1) from Exam where $partition.PF_Exam(UserID)=2
*/
CREATE UNIQUE CLUSTERED INDEX [Exam_UserId] ON [dbo].[Exam]
(
[UserId] ASC,
[Content] ASC
)WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON PS_Exam(UserId)
--设置锁定
ALTER TABLE [Exam] SET (LOCK_ESCALATION = AUTO)
GO
--***********************<CHOOSE1重建聚集索引/END>**************************************
--***********************<CHOOSE2加载数据>*****************************************
--创建分区表
if OBJECT_ID('Exam_Part','U') is not null
drop table Exam_Part
go
CREATE TABLE [Exam_Part](
[ExamId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[ExamTypeId] [int] NOT NULL,
[Content] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[RowGuid] [uniqueidentifier] NOT NULL,
[PinYin] [varchar](300) COLLATE Chinese_PRC_CI_AS NOT NULL,
) ON PS_Exam(UserId)
GO
--加载数据
/*
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('Exam_Part')
select * from Exam_Part where $partition.PF_Exam(UserID)=1
*/
set IDENTITY_INSERT [Exam_Part] on
insert [Exam_Part](ExamId,UserId,Content,PinYin)
select ExamId,UserId,Content,PinYin from Exam where UserId<40
set IDENTITY_INSERT [Exam_Part] off
--创建索引
/*
SELECT OBJECT_NAME(id),* from sys.sysindexes where OBJECT_NAME(id)='Exam_Part'
*/
if OBJECT_ID('Exam_Part_PK','PK') is not null
alter table [Exam_Part] drop CONSTRAINT Exam_Part_PK
go
ALTER TABLE [Exam_Part] ADD CONSTRAINT [Exam_Part_PK] PRIMARY KEY NONCLUSTERED
(
[UserId] Asc,
[ExamId] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON PS_Exam(UserId)
if exists(select 1 from sys.sysindexes where name='Exam_UserId' and OBJECT_NAME(id)='Exam_Part')
drop index Exam_UserId on Exam_Part
go
CREATE UNIQUE CLUSTERED INDEX [Exam_UserId] ON [Exam_Part]
(
[UserId] ASC,
[Content] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON PS_Exam(UserId)
if exists(select 1 from sys.sysindexes where name='Exam_ModifiedDate' and OBJECT_NAME(id)='Exam_Part')
drop index Exam_ModifiedDate on Exam_Part
go
CREATE NONCLUSTERED INDEX [Exam_ModifiedDate] ON [Exam_Part]
(
[UserId] ASC,
[ModifiedDate] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON PS_Exam(UserId)
--重命名表
--**********************<CHOOSE2加载数据/END>*****************************************
--***********************<CHOOSE3切换分区>*****************************************
--创建单分区表
if exists(Select 1 from sys.partition_schemes where name='PS_Exam0')
drop partition scheme PS_Exam0
go
if exists(Select 1 from sys.partition_functions where name='PF_Exam0')
drop partition function PF_Exam0
go
create partition function PF_Exam0(int)
as range left
for values()
go
create partition scheme PS_Exam0
as partition PF_Exam0
all to ([Primary])
go
if OBJECT_ID('Exam_Part0','U') is not null
drop table Exam_Part0
go
CREATE TABLE [Exam_Part0](
[ExamId] [int] IDENTITY(1,1) NOT NULL,) ON PS_Exam0(UserId)
[UserId] [int] NOT NULL,
[ExamTypeId] [int] NOT NULL,
[Content] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[RowGuid] [uniqueidentifier] NOT NULL,
[PinYin] [varchar](300) COLLATE Chinese_PRC_CI_AS NOT NULL,
GO
if exists(select 1 from sys.sysindexes where name='Exam_UserId' and OBJECT_NAME(id)='Exam_Part0')
drop index Exam_UserId on Exam_Part0
go
CREATE UNIQUE CLUSTERED INDEX [Exam_UserId] ON [Exam_Part0]
(
[UserId] ASC,
[Content] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON PS_Exam0(UserId)
--删除约束
--[ExamsInGroup]
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Exam_ExamsInGroup_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ExamsInGroup]'))
ALTER TABLE [dbo].[ExamsInGroup] DROP CONSTRAINT [Exam_ExamsInGroup_FK1]
go
ALTER TABLE [dbo].[ExamsInGroup] WITH NOCHECK ADD CONSTRAINT [Exam_ExamsInGroup_FK1] FOREIGN KEY([ExamId])
REFERENCES [dbo].[Exam] ([ExamId])
ON UPDATE CASCADE
ON DELETE CASCADE
go
--[Exam]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Exam]') AND name = N'Exam_PK')
ALTER TABLE [dbo].[Exam] DROP CONSTRAINT [Exam_PK]
go
ALTER TABLE [dbo].[Exam] ADD CONSTRAINT [Exam_PK] PRIMARY KEY NONCLUSTERED
(
[ExamId] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
--切换分区
/*
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('Exam_Part0')
select count(1) from Exam_Part0 where $partition.PF_Exam0(UserID)=1
select $partition.PF_Exam0(100)
select * from Exam where $partition.PF_Exam0(UserID)=1
*/
alter table Exam
switch to Exam_Part0 partition 1
go
go
--创建索引
if OBJECT_ID('Exam_Part0_PK','PK') is not null
alter table [Exam_Part0] drop CONSTRAINT Exam_Part0_PK
go
ALTER TABLE [Exam_Part0] ADD CONSTRAINT [Exam_Part0_PK] PRIMARY KEY NONCLUSTERED
(
[UserId] ASC,
[ExamId] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON PS_Exam0(UserId)
go
if exists(select 1 from sys.sysindexes where name='Exam_ModifiedDate' and OBJECT_NAME(id)='Exam_Part0')
drop index Exam_ModifiedDate on Exam_Part0
go
CREATE NONCLUSTERED INDEX [Exam_ModifiedDate] ON [Exam_Part0]
(
[UserId] ASC,
[ModifiedDate] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON PS_Exam0(UserId)
--拆分分区
alter partition scheme PS_Exam0
next used FG_Exam1
go
alter partition function PF_Exam0()
split range (0)
go
alter partition scheme PS_Exam0
next used FG_Exam1
go
alter partition function PF_Exam0()
split range (40)
go
--合并分区
alter partition function PF_Exam0()
merge range (40)
go
alter partition function PF_Exam0()
merge range (0)
go
--切换分区
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Exam]') AND name = N'Exam_ModifiedDate')
DROP INDEX [Exam_ModifiedDate] ON [dbo].[Exam] WITH ( ONLINE = OFF )
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[User_Exam_FK1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Exam]'))
ALTER TABLE [dbo].[Exam] DROP CONSTRAINT [User_Exam_FK1]
GO
alter table Exam_Part0
switch partition 1 to Exam
go
--***********************<CHOOSE3切换分区/END>*****************************************