小说收藏表做分区SQL记录

ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_0;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_1;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_2;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_3;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_4;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_5;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_6;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_7;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_8;
ALTER DATABASE NovelDB2 ADD FILEGROUP FG_FUIDS_9;

ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_0', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_0.NDF') TO FILEGROUP FG_FUIDS_0;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_1', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_1.NDF') TO FILEGROUP FG_FUIDS_1;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_2', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_2.NDF') TO FILEGROUP FG_FUIDS_2;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_3', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_3.NDF') TO FILEGROUP FG_FUIDS_3;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_4', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_4.NDF') TO FILEGROUP FG_FUIDS_4;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_5', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_5.NDF') TO FILEGROUP FG_FUIDS_5;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_6', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_6.NDF') TO FILEGROUP FG_FUIDS_6;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_7', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_7.NDF') TO FILEGROUP FG_FUIDS_7;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_8', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_8.NDF') TO FILEGROUP FG_FUIDS_8;
ALTER DATABASE NovelDB2 ADD FILE (NAME = 'File_FUIDS_9', FILENAME = 'E:\SqlData\NovelDB2\NovelDB2_T_MyAttention_Partition_FUIDS_9.NDF') TO FILEGROUP FG_FUIDS_9;

use NovelDB2
GO

CREATE PARTITION FUNCTION FUIDS_PartitionFunction(int)
AS
RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9)

CREATE PARTITION SCHEME FUIDS_Partitionstruct
AS PARTITION FUIDS_PartitionFunction TO (FG_FUIDS_0, FG_FUIDS_1, FG_FUIDS_2, FG_FUIDS_3, FG_FUIDS_4, FG_FUIDS_5, FG_FUIDS_6, FG_FUIDS_7, FG_FUIDS_8, FG_FUIDS_9)

GO

--删除聚集索引
drop index PK_T_MyAttention  ON T_MyAttention

GO

--增加聚集索引,并移动数据
ALTER TABLE T_MyAttention ADD
CONSTRAINT PK_T_MyAttention
PRIMARY KEY (F_UID,[AutoID])
ON FUIDS_Partitionstruct(F_UID)

 

 

 

【这个是直接在现有表基础上从 普通表转分区表】

 

 

==========

 

 

【这个是建立一个新的分区表，然后将旧表数据导入新分区表，然后将旧表删除，然后重命名新表名字为旧表名字】

 

ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_0;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_1;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_2;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_3;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_4;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_5;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_6;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_7;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_8;
ALTER DATABASE NovelDB ADD FILEGROUP FG_FUID_9;

ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_0', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_0.NDF') TO FILEGROUP FG_FUID_0;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_1', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_1.NDF') TO FILEGROUP FG_FUID_1;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_2', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_2.NDF') TO FILEGROUP FG_FUID_2;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_3', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_3.NDF') TO FILEGROUP FG_FUID_3;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_4', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_4.NDF') TO FILEGROUP FG_FUID_4;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_5', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_5.NDF') TO FILEGROUP FG_FUID_5;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_6', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_6.NDF') TO FILEGROUP FG_FUID_6;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_7', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_7.NDF') TO FILEGROUP FG_FUID_7;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_8', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_8.NDF') TO FILEGROUP FG_FUID_8;
ALTER DATABASE NovelDB ADD FILE (NAME = 'File_FUID_9', FILENAME = 'E:\SqlData\NovelDB_T_MyAttention_FUID_9.NDF') TO FILEGROUP FG_FUID_9;

use NovelDB
GO

CREATE PARTITION FUNCTION FUID_PartitionFunction(int)
AS
RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9)

CREATE PARTITION SCHEME FUID_Partitionstruct
AS PARTITION FUID_PartitionFunction TO (FG_FUID_0, FG_FUID_1, FG_FUID_2, FG_FUID_3, FG_FUID_4, FG_FUID_5, FG_FUID_6, FG_FUID_7, FG_FUID_8, FG_FUID_9)

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_MyAttention_Partition](
 [UID] [bigint] NOT NULL,
 [ObjID] [bigint] NOT NULL,
 [ObjType] [int] NOT NULL,
 [Action] [int] NULL,
 [AddTime] [datetime] NOT NULL,
 [TrueName] [varchar](100) NULL,
 [Imei] [varchar](50) NULL CONSTRAINT [DF_T_BookAttention_Imei]  DEFAULT (''),
 [AutoID] [bigint] IDENTITY(1,1) NOT NULL,
 [F_UID] [int] NOT NULL CONSTRAINT [DF_T_MyAttention_F_UID]  DEFAULT ((0))
) ON FUID_Partitionstruct(F_UID)

GO
SET ANSI_PADDING OFF
GO
CREATE CLUSTERED INDEX [PK_T_MyAttention] ON [dbo].[T_MyAttention]
(
 [AutoID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_IMEI_BookAttention] ON [dbo].[T_MyAttention]
(
 [Imei] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UID_BookAttention] ON [dbo].[T_MyAttention]
(
 [UID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

 

 

 

==========

 

查看分区数据分布结构

 

select partition = $partition.FUID_PartitionFunction(F_UID)
      ,rows      = count(*)
      ,minval    = min(F_UID)
      ,maxval    = max(F_UID)
  from dbo.T_MyAttention
 group by $partition.FUID_PartitionFunction(F_UID)
 order by partition